Microsoft KB Archive/236003

= BUG: DB-Library bcp_sendrow Fails on SQL 7.0 =

Article ID: 236003

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q236003



BUG #: 55022 (SQLBUG_70)



SYMPTOMS
The DB-Library bcp function bcp_sendrow fails with the following error message:


 * 1) @ Row 1, Column NNN: The row length exceeds SQL Server's maximum allowable size. @#

"NNN" in the preceding example is 1 more than the actual number of columns in the table. This error is seen for every row. The same DB-Library application worked fine with Microsoft SQL Server 6.5 or 6.0.



CAUSE
The cause of this problem is a change in the way that SQL Server 7.0 stores NULLable columns when compared to earlier versions of SQL Server.
 * In 6.5 servers, NULLable types are considered variable-length; they do not contribute to the calculated minimum length of a row.


 * In 7.0, types such as CHAR (as opposed to VARCHAR), money, and so forth are represented as fixed length. They always contribute to the minimum row length, even if the column can contain NULLs.

This is an issue because DB-Library has not been enhanced to support new SQL Server 7.0 features. When a client defines a table that contains many NULLable columns (say, "CHAR(50) NULL"), this has a much more significant impact on the calculated minimum row length than it did in 6.5, causing any 6.5 DB-Lib client to think that it has less room in the table than is actually available. In this case, the DB-Library application thinks that the maximum size of a row is 1962 bytes (the maximum in SQL Server 6.5), while the actual maximum row size in SQL Server 7.0 is 8060 bytes.



WORKAROUND
One workaround is to change the data type for any nullable CHAR columns in this table to VARCHAR, and change any nullable BINARY columns to VARBINARY. The change causes the minimum row length to be calculated similarly to 6.5 for these columns.

You should also consider converting the process to use the ODBC or OLEDB APIs, which provide functionality that is superior to the feature set exposed by DB-Library. DB-Library has not been enhanced to take advantage of new SQL 7.0 functionality such as Unicode data types, long rows, 8000 byte CHAR/VARCHAR columns, and so forth.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.