SQL Stored Proc Collation Context

Question

Hey, I'm having an issue that appears to be related to collation, but I'm not sure.

I'm developing against a backup of my production database. The only difference in the SQL Server 2005 environments (that I'm aware of right now) is that my localhost has "SQL_Latin1_General_CP1_CS_AS" (Case sensitive) as the collation, and the production environment has "SQL_Latin1_General_CP1_CI_AS" (case insensitive).

When I execute the stored procedures locally, it is throwing "Must Declare Scalar Variable "@firstName"" errors.

The variable "@FirstName" has been declared.

Now, the database itself IS "SQL_Latin1_General_CP1_CI_AS" as the collation, so why are the stored procedures not running in a case insensitive context?

I've looked into this online, and they say basically that I have to rebuild my SQLserver.

Shouldn't the database context trump the server context for collation?

BTW, the master and tempdb are both "SQL_Latin1_General_CP1_CS_AS" Case Sensitive.

Answer 1

This is a BAD situation to be in. Anything where you don't control the collation (e.g. SELECT INTO a temporary table) will break when you try to do anything involving varchars. The server default collation will trump anything where you don't explicitly coerce the collation, which will be most if not all of your existing stored procedure code.

The normal solution to this problem is to reinstall the SQL Server instance with the correct collation. Yes, you really do need to do that. My knowledge of this is secondhand, but I understand (from someone who got MS technical support involved in this issue) is that it is technically possible to change the default collation on a SQL Server instance but the process is technical and error prone, and making a mistake can leave the server in an unusable state. Apparently this procedure is not officially supported by Microsoft and the recommended procedure is to reinstall the SQL Server instance.