I had an interesting problem the other day.

Someone ran a sql script I had written on their SQL2005 database and it threw an error like so:

Must declare the scalar variable "@profileId".

Upon investigating the only thing I could see that was wrong was I had declared the variable like so:

[csharp]Declare @profileID as int[/csharp]

The issue was the case of the variables was different (the “ID” part of my identifier).

Digging around I found the following truth…

A T-SQL variable will default to the Collation of the master database. So if you chose a case sensitive collation when you installed SQL Server  then the master database will have this collation and you will run into theses issues.

This problem occurs regardles of the Collation used on the database you may be executing the SQL statement against.

To check the case sensitivity of your master database, run:

[sql]Exec master.dbo.sp_server_info[/sql]

and look at attribute 16 – IDENTIFIER_CASE, if it says SENSITIVE you have a collation like Latin1_Bin or Latin1_CS_AS (CS stands for Case senstive), MIXED is a case-insenstive collation.

You cannot alter the collation of the master database, so it is backup time, uninstall/re-install SQL server with a case insensitive Collation, (default) and restore your databases, if you want to be rid of this issue.

Cheers