BUG: DB-Library bcp_sendrow() Fails on SQL 7.0

ID: Q236003

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55022 (SQLBUG_70)

SYMPTOMS

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

#@ 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 version 7.0.

REFERENCES

For more information about the DB-Library, see the following article in the Microsoft Knowledge Base:

Q174817 INF: Microsoft SQL Server DB-Library Has Limited Extensibility

Additional query words: dblib db-lib dblibrary db-library bcpsendrow bcp_sendrow function fcn

Keywords : SSrvBCP SSrvDB_Lib kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug