When using a SELECT statement to query data from Microsoft SQL Server you may find that a “large data” or BLOB datatype column (NTEXT, TEXT, varchar(max), nvarchar(max), varbinary(max) or image) can be truncated probably to 4096k. Why is is this? And how can I fix it?
Firstly this is not an issue with Iguana, as Iguana places no limits on string lengths. Therefore the error must be with the data returned by the database.
Note: This FAQ assumes that you are familiar with SQL Server.
From reading Microsoft documentation about these datatypes, the following occurs:
- The “large data” column is bound to a program variable when you make a SELECT query
- By default this program variable is 4096k in length, as specified by TEXTSIZE setting (default = 4096k)
- You can set the length using the @@TEXTSIZE system function
- This applies when you use an API such as ADO, OLE DB, or ODBC
Result: When you query an NTEXT (or other “large data”) field the amount of data is restricted/truncated the to the length specified in TEXTSIZE (default = 4096k).
Solution: Use @@TEXTSIZE to increase the length so that all of the data in your field is returned.
Why not just return all the data by default? Performance! Many BLOB fields are mostly empty, TEXTSIZE allows you retrieve only the part you need. As opposed to sending mostly blank data in the field and greatly increasing network/processing overhead (remember these fields are able to store a huge amounts of data, so you could easily increase overheads by hundreds or thousands of times).
More Information
- Microsoft Technet > SQL Server: Retrieving ntext, text, or image Values
- Microsoft Technet > SQL Server: Using Large-Value Data Types
- Try a Google search for “TEXTSIZE limit the amount of text displayed” or similar