Benjamin Day Consulting, Inc.: The Blog

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

The last few days have been a little bit hellish.  I've been on-site at my client's office helping them do a Windows 2000 to Windows 2003 migration, set up their new server, and deploy beta versions of the applications that I've been writing for them. 

I backed up their SQL Server database from the old server and restored it onto the new Windows 2003 server.  It restored fine.  I could connect using Query Analyzer and any ADO.NET-based application that was running locally could connect.  It's looking good then we start testing some ASP.NET apps that are on a different machine and hit this new database server.  BLAM!  We started getting exceptions.  Either “Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection” or “Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.” 

First order of business: go to google and start digging.  Very quickly I found this tech support article saying that SQL Server isn't set to “mixed mode” authentication.  I checked it out and the sysadmin had installed SQL Server to “Windows only“ mode.  That's easy enough to change.  Changed it and restarted the MSSQLSERVER service. 

No love.  Now I start wondering if there's something messed up in the registry.  I find this article describing how to manually change SQL Server to “mixed mode.”  Nope.  That's already done.  The documentation says that we only have to cycle the instance for the change to take but we reboot the whole server just to make sure. 

Still nothing.  At this point, we've burned up a lot of time on something that should be totally easy.  Now I start wondering if it's a security problem because I kinda half remembered reading about distributed transaction problems between Windows 2000 and instances of SQL Server running on Windows 2003. 

None of it was really looking likely and at some point I started thinking about how SQL Server isn't supported on Windows 2003 under a certain service pack level.  What service pack is this server running anyway?  So, I fired up Query Analyzer and ran “SELECT @@VERSION“.  The version was “2000.80.194.0“.   Quick skim of the build numbers listed online and...UNBELIEVABLE!  This thing hasn't been patched even ONCE!

So, I upgraded to SQL Server Service Pack 4 and my problems went away.  While I was at it, I also turned on Windows Update and found out that none of the security patches had be applied either.  Doh.

-Ben