SQL Server 2000 Requires Explicit Declaration Of Data Types In Client App After Installing A Certain HotFix

I think I may have figured out the solution to my recent conundrum with SQL Server 2000 that had me stressed and depressed over the last couple of days. In a nutshell, after a HotFix was installed on a SQL2K database server I have space on, I was unable to perform INSERT or UPDATE queries on database table of type TEXT, when trying to either create or modify records with more than 4,000 characters of data. While it was frustrating as heck, it seemed too rigid to be random, so I did some snooping.





The HotFix was intended to solve a known problem of not being able to run UPDATEs against TEXT fields, but in so doing, caused another headache entirely: http://support.microsoft.com/?kbid=839523





Apparently this is a semi-known problem, in that a certain HotFix forces SQL Server 2000 to be a lot more stringent in requiring explicit declaration of data types and data lengths for parameters in stored procedure. In my client code, I was initially using the overloaded constructor of the SqlParameter object that took as arguments only the parameter name and a value, without specifying a value from the SQLDBType enumeration or length of the parameter (which in my case, needs to be TEXT and 16 (or 2147483647), respectively):





System.Data.SqlDataAdapter.InsertCommand.Parameters.Add(new SqlParameter("@parameterName",parameterValue);





It appears that after the HotFix is installed, if the client doesn’t syntactically set the type and length of data for a parameter, SQL Server and/or .NET will default to a type of NVARCHAR, which has the 4,000-character limit. This all makes sense. I’m going to now need to modify the code to straight out declare what’s going in the SPROC:





System.Data.SqlDataAdapter.InsertCommand.Parameters.Add(new SqlParameter("@parameterName",SqlDbType.Text);


System.Data.SqlDataAdapter.InsertCommand.Parameters["@parameterName"].Value = parameterValue;





It’s a minor change, and it sucks that I have to make it after the code had worked flawlessly over several thousands executions over several months, but c’est la vie! Better thay than have to rebuild my DB from scratch or switch to a new server. Changing the client code evidently is the only known fix at this time: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b827366