Home > SQL Server > Case Sensitive T-SQL (TSQL) variables

Case Sensitive T-SQL (TSQL) variables

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:

Declare @profileID as int

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:

Exec master.dbo.sp_server_info

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

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: