Unfortunately the stricter datatype processing is a side effect of the 031
patch. We're working on a KB article to explain the behavior and scope.
Here is a draft of our work in progress:
KB 827366 ? ?Error 17805: Invalid Buffer Received from Client? Error
Message in SQL?
----------------------------------------------------------------------------
---
The information in this article applies to:
- Microsoft .NET Framework 1.0 (Version: 1.0)
- Microsoft .NET Framework 1.1
----------------------------------------------------------------------------
---
SYMPTOMS
========
When you use the SqlClient .NET Framework classes, the following error
messages may appear in the SQL Server 2000 error log:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
The following corresponding errors may appear in the client .NET
application:
System.Data.SqlClient.SqlException: A severe error occurred on
the current command. The results, if any, should be discarded
-or-
System.Data.SqlClient.SqlException: Procedure or function
spXXXX has too many arguments specified.
Note If you are using the .NET Framework 1.1 you only see the last error
message.
CAUSE
=====
There are three causes for these errors:
- You use SqlClient classes in a Finalize method or C# destructor. Do not
use any managed classes in a Finalize method or C# destructor.
- You do not specify an explicit SQLDbType for the parameters. In this
case, the SqlClient .NET provider tries to select the correct SQLDbType
based on the data that is passed and it will fail.
- If the size of the parameter that is specified explicitly in the .NET
code is more than the maximum allowable size for the data type in the SQL
Server.
- For example: According to SQL Server Books Online, nvarchar is a
Variable-length Unicode character data of n characters. n must be a value
from 1 through 4,000 If you specify a size that is more than 4000 for an
nvarchar parameter, then you will receive the error message that the
"Symptoms" section describes.
The following code also demonstrates how these errors can occur:
Stored Procedure
--------------------------
PROCEDURE spParameterBug @myText Text AS
Insert Into ParameterBugTable (TextField) Values
(@myText)
Code
-------
static void Main(string[] args)
{
string dummyText=string.Empty;
for (int n=0; n < /*80*/ 3277; n++) // change this to
80 to get the second error above
{
dummyText += "0123456789";
}
// TO DO: Change data source to match your SQL Server:
SqlConnection con= new SqlConnection("data
source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;persist
security info=True;packet size=16384");
SqlCommand cmd = new SqlCommand("SpParameterBug", con);
// Correct invocation:
SqlParameter param =new SqlParameter("@myText",
SqlDbType.Text);
param.Value = dummyText;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(param);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Console.WriteLine(err.ToString());
}
// Causes error 17805:
SqlParameter param2 =new SqlParameter("@myText",
dummyText);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(param2);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Console.WriteLine(err.ToString());
}
Console.ReadLine();
}
RESOLUTION
==========
To resolve these errors, make sure that you do the following:
1. Do not use SqlClient classes in a Finalize method or a C# destructor.
2. Specify the SqlDbType for the SqlParameter so that there is no inferred
type.
3. Specify a parameter size that is within the allowable limits of the data
type.
REFERENCES
==========
For more information about the maximum size for different data types, see
these sections of SQL Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
na-nop_9msy.asp: nchar and nvarchar
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
da-db_7msw.asp: Data Types
Shawn Aebi
Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
