FIX: Large SQL Statement Fails When Using 512-Byte Packet Size (299915)

SYMPTOMS

When using the SQL Server ODBC driver or SQL Server OLEDB provider (SQLOLEDB), a large SQL statement (where the length of the SQL statement exceeds 5 MB) may fail to execute. No error is reported to the client application when this specific problem occurs. The problem is more readily reproduced when a smaller than normal network packet size is used; for example, when using a 512-byte network packet size. Note that the default network packet size for these drivers is 4096 bytes.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

The best way to avoid this problem is to reduce the size of your SQL statement. For example, rather than inlining a large binary constant in your SQL statement, you can use a parameter token (?) and supply the binary data using a parameter.

You can also avoid this problem by increasing the network packet size to a larger value, such as 8192 bytes or even larger. Note however that the default network packet size of 4096 bytes is recommended for most applications for optimal overall performance.

If the query is a stored procedure call, you may also avoid this problem by using the call escape sequence, as in the following example.

Original query:

Exec sp_test 1,0x000000000000...000

Change to:

{call sp_test(1,0x000000000000...000)}

MORE INFORMATION

With the SQL Server OLEDB provider (SQLOLEDB), you can adjust the network packet size by setting the SSPROP_INIT_PACKETSIZE property in the DBPROPSET_SQLSERVERDBINIT property set. With the SQL Server ODBC driver, you can adjust the network packet size by using the SQLSetConnectOption or SQLSetConnectAttr ODBC API function (using the SQL_PACKET_SIZE or SQL_ATTR_PACKET_SIZE flag). By default, both drivers use 4096 for the packet size.

A very large SQL statement can be generated by inlining a very large binary constant, as in the following example SQL statements:

create table bintest(f1 image)
-- Insert 5 MB of binary data using an inline constant (ascii
-- representation of binary data).
insert into bintest (f1) values (0xFF00EE00FF00EE00...FF00EE)

Third-party native JDBC SQL Server drivers may inline binary data in this fashion when using parameterized INSERT statements.