what is the maximum length of characted can you have in a cursor select SQL statement in SQLServer 2000?

Question

I keep getting an truncation error when selecting from 2 temp tables which have 4 colunms defined as varchar(1000) amongst others.

Answer 1

sizeof(string) which is damn near infinite. If your not getting all your data back, it aint cause of the size limit of the returning data, but probably your environment (dev and server)

Answer 2

the cursor creates a temporary table so the limit is the same as a regular table i.e. the whole row must fit within a single datapage. A datapage is 8KB so ignoring text datatype that's about 1000 columns of char(8000)

Perhaps in your select statement you are casting a field to another datatype which is too small for the data? cast(myvarchar as varchar(1000)) where myvarchar is actually 2000 chars long?

I would perhaps rethink your strategy, is there really any need to be putting such large amounts of data into a cursor? This is goign to be slow and memory intensive, even assuming you're using an RO cursor. Cursors as a rule should be avoided where possible but I appreciate there are (rare) times they are necessary.

Answer 3

It's surely not that the lengths of the select statement creates a truncation error. Look rather if only the output is truncated.

Helmut