Microsoft KB Archive/895203

= Assigning a binary large object data type to an input parameter of a stored procedure may fail when the data is large in SQL Server 2000 =

Article ID: 895203

Article Last Modified on 3/14/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



SYMPTOMS
In Microsoft SQL Server 2000, when you try to assign a binary large object data type from an application to an input parameter of a stored procedure, the assignment operation may fail when the data is large. This behavior occurs when the data type is image, text, or ntext. When you try to assign the data, you may receive one of the following error messages in the SQL Server 2000 error log:

Error: 17803, Severity: 20, State: 8 Insufficient memory available.

Error: 17805, Severity: 20, State: 2 Invalid buffer received from client.

Note To determine whether you are affected by this behavior, run the DBCC Memorystatus command in SQL Query Analyzer. When you run the DBCC Memorystatus command, you can see the increase in Stolen buffers and Connection buffers while the application is running. You will see the incremental increase in the values every time that the command is refreshed for these memory buffer pool counters.



CAUSE
This behavior occurs because the memory that is required to store the parameter is larger than the available contiguous memory in the SQL Server 2000 buffer pool. The parameter must be able to fit in memory that is available in a contiguous block. The large binary large object depends on the max server memory option that you set for the SQL Server 2000 instance and the memory that is available in the buffer pool. Typically, the range can start at 4 MB and larger.

Note You use the max server memory option to set memory requirements of your SQL Server-based server to prevent SQL Server from using more than the specified amount of memory. The SQL Server does not immediately allocate the memory that is specified in themax server memory option on startup. Instead, memory usage is increased as needed by SQL Server until it reaches the value that is specified in the max server memory option. The SQL Server cannot exceed memory usage unless you increase the value of the max server memory option.



WORKAROUND
To work around this behavior, write the binary large object data directly into the table instead of assigning the binary large object data to a stored procedure parameter.

