In my last post, I wrote about the process of discovering that my client's SQL Server hadn't had a single service pack applied to it. We applied Service Pack 4 and good times were had by all....for about 12 hours. The web applications all worked but then a .NET Windows application started serving up strange errors whenever the user attempted to save any records to the database.
Error #1:
Database call to '' caused an exception. ---> System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
Error #2:
Database call to 'ConnectionWrite (send()).' caused an exception. ---> System.Data.SqlClient.SqlException: General network error. Check your network documentation.
Those are definitely serious looking errors but when I traced it down to the actual command being issued in the code, it was just a simple SqlDataAdapter.Update(). This code has been in production for months and died right after we applied the SQL Server Service Pack 4 patch. What's up with that?
I looked in the SQL Server logs and two suspicious messages started showing up right after the user hits “Save”.
Message #1:
Error: 17805, Severity: 20, State: 3
Message #2:
Invalid buffer received from client.
Back to google.com for some research and I found this post:
Sp4 reduces the network packet size to 32767. Consider adjusting your app to
avoid the error.
In SP4, the maximum value for the network packet size option (set using
sp_configure) is 32767. This is slightly less than half the previous maximum
of 65536. During upgrade, existing values larger than 32767 will
automatically be adjusted to 32767. If a script attempts to use sp_configure
to set a value larger than 32767 but less than or equal to 65536, the value
will also be set to 32767. Setting the network packet size to a value larger
than 65536 results in an error.
Ok. Awesome. Now I know what's gone wrong...now how to fix it. I found this post suggesting a tweek to some of the startup parameters in SQL Server but it didn't work. Then I found this one suggesting that I add “Packet Size=4096;” to my connection string. (Huh...never heard of that option.) That didn't work either. But at least now I have an idea that it's something to do with the data type enumeration or the size of the data being passed to a stored procedure parameter.
Man! Did I ever luck out. I found the bug in the code in about 2 minutes. I must have changed the target column's data type from varchar(8000) to nvarchar(4000) at some point. I found that I was trying to add a parameter to the SqlCommand as SqlDbType.NVarChar with a length of 8000. Definitely a violation since nvarchar's max length is 4000. I changed it to length=4000, recompiled, and the problem was solved.
-Ben
posted @ Thursday, June 16, 2005 8:12 PM