Microsoft KB Archive/299915

= FIX: Large SQL Statement Fails When Using 512-Byte Packet Size =

Article ID: 299915

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q299915



BUG #: 235783 (SHILOH_BUGS)



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)}



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 1.



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.

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB299915

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.