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:
"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.
REFERENCES
For more information about the DB-Library, see the following article in the Microsoft Knowledge Base:
174817 INF: Microsoft SQL Server DB-Library Has Limited Extensibility
Additional query words: dblib db-lib dblibrary db-library bcpsendrow bcp_sendrow function fcn
Keywords: kbbug kbpending KB236003
