Database call to 'ConnectionWrite (send()).' caused an exception.

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