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 (895203)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
Bug #: 472908 (SQL Server 8.0)
SQL Server 8.0:472908

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.

REFERENCES

For more information about binary large object input parameters, click the following article number to view the article in the Microsoft Knowledge Base:

293911 SAMPLE: ABParam.exe passes a BLOB parameter with Active Template Library

317034 How to read and write a file to and from a BLOB column by using chunking in ADO.NET and Visual Basic .NET

317044 How to read and write a file to and from a BLOB column by using chunking in ADO.NET and Visual C++ .NET

Modification Type:MajorLast Reviewed:4/13/2005
Keywords:kbTSQL kbnofix kbinfo kbprb KB895203 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.