At a company I work for we recently had to downgrade from the 64 bit version of Microsoft SQL Server 2005 back to the 32 bit version (long story :) ).  After removing the 64 bit version of SQL Server and installing the 32 bit version and then recreating the master db and setting up all of the databases again, Optimum no longer worked correctly.  I could log in fine, but whenever I tried to open up Employees, or just about anything else, I got a big nasty error message saying “you must declare the scalar variable @USERID”.  The actual error message displayed contained the complete stack trace and was actually about a full page of text.  Unfortunately I didn’t copy the entire error message before I fixed the problem, and I’m not going to break it again just to be able to show the full error here.  :)

To find the solution to the problem I used SQL Server Profiler to do a trace on the SQL Server and see what was going on when the error occurred.  I was able to find out what stored procedure the error was being generated in.  Then I opened up that stored procedure in SQL Server Management Studio and had a look at the code.  Looking at the code I noticed that the stored procedure had “@UserID” declared as an input variable.  But the error message says “@USERID”.  Hmmm…  :)  So I look a few lines down and sure enough the variable is being referenced as “@USERID” instead of “@UserID”.  After searching Google for “sql server 2005 case insensitive variable” and poking around a bit I discovered that in order for this to work the default collation for the SQL Server (not just the collation for the Optimum database) needs to be case insensitive.

Unfortunately the only way to change the default collation for the SQL Server is to rebuild the master database.  This is really not something you want to ever have to do, especially if you’ve heavily configured your SQL Server and/or have lots of databases and logins and other stuff residing in the master database.  I found a good article describing all the steps that should be taken before rebuilding the master database, to ensure that you have all of the data you need saved somewhere else so you can restore it afterward.  I highly recommend reading that article before even thinking about rebuilding the master database.  And of course you want to make sure you have full backups of everything, but you already do right?  Better double check just to be sure.

To rebuild the master database you need to use setup.exe from your SQL Server installation CD.  You’ll use a command similar to the following:

start /wait [CD Drive]\setup.exe /qn INSTANCENAME=[SQL Server Instance] REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=[SA Password] SQLCOLLATION=[New Default Collation]

Replace [CD Drive] with the letter of your CD-ROM drive.  Replace [SQL Server Instance] with the name of the SQL Server instance you are recreating the master database for.  The default instance is “MSSQLSERVER”.  Replace [SA Password] with the password you want to use for the SA login.  Replace [New Default Collation] with the new default collation you want to use for this SQL server instance.  Optimum uses “SQL_Latin1_General_CP1_CI_A”.  When you’re sure you’re ready, put your installation CD in the drive, open up a cmd prompt and execute the command.  Obviously no one else can be using the SQL Server while you are doing this.  Here is the command I used (except the SA password is fake):

start /wait d:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=password SQLCOLLATION=SQL_Latin1_General_CP1_CI_A

This command will not output anything to the screen.  Just have faith and let it chug away until it returns you to the command prompt.  If you want to be able to see what’s going on, you can replace the “/qn” switch with “/qb”.  This will cause basic setup dialog boxes including error messages to be displayed.

Once all this is done you still need reconfigure the master db and restore all your stuff, reattach your databases, etc.  Once all that is done Optimum should be back to “normal” again.