This Blog Has Been Moved !

This is something really intersting, atleast for me this is completely new and is still sort of mystery like what’s going on.  Today I deployed a SQL Server 2005 Database on one of the test servers at office and during the test my application started crashing. I was getting declaration errors form the database.

“Must declare Variable @ApplicaitonID “

It appeared that all the variables were declared but they were declared in difference a CASE and used in different CASE, well this should’nt be a problem, I have been using T-SQL without case sensitivity for long, but when I corrected the case everything seemed fine. The EXEC master.dbo.sp_server_info returned the following results (given at the end) and you can see the IDENTIFIER_CASE parameter set to SENSITIVE, Actually it should be MIXED. Anyway I tried to find how to change this parameter but it seems that there is no direct way to change it.

This parameter depends on the Collation so apperently there is a problem with the Collation, ok now I have to change the Collation to SQL_Latin1_General_CP1_CI_AS From Latin1_General_BIN. Now chaning collation on the database server is a long hard process, you have backup all your data and tables and reinstall the Database Engine with different collation and restore everything back.

The First thing I do not understand is that why collation is applied on the Variable Names (T-SQL Syntax), I always thought collation was something only for the comaprison/Case-Sensitivity of database data and something which will not be used for procedure compilation. I do not understand this and I’ll have to find some answer for this.

Secondly, SQL Server 2005 supports different collation settings for each database and the Database Engine itself, and the collation settings on the copied database was fine. I even rechecked it and even tried to set it again but still even with the correct collation I continued to get the same Errors, I opened a procedure from database with the correct collation in Management Studio and tried recompiling it, but it was giving a recompilation error because of the case insensitivity. If SQL Server 2005 supports different Collation settings for Server and each individual database why I keep getting this annoying error.

If some can explain this to me I will really appreciate.

EXEC master.dbo.sp_server_info

attribute_id attribute_name               attribute_value

------------ ---------------------------- ---------------------------------------

1            DBMS_NAME                    Microsoft SQL Server

2            DBMS_VER                     Microsoft SQL Server Yukon - 9.00.1399

10           OWNER_TERM                   owner

11           TABLE_TERM                   table

12           MAX_OWNER_NAME_LENGTH        128

13           TABLE_LENGTH                 128

14           MAX_QUAL_LENGTH              128

15           COLUMN_LENGTH                128

16           IDENTIFIER_CASE              SENSITIVE

17           TX_ISOLATION                 2

18           COLLATION_SEQ                charset=iso_1 collation=Latin1_General_BIN

19           SAVEPOINT_SUPPORT            Y

20           MULTI_RESULT_SETS            Y

22           ACCESSIBLE_TABLES            Y

100          USERID_LENGTH                128

101          QUALIFIER_TERM               database

102          NAMED_TRANSACTIONS           Y

103          SPROC_AS_LANGUAGE            Y

104          ACCESSIBLE_SPROC             Y

105          MAX_INDEX_COLS               16

106          RENAME_TABLE                 Y

107          RENAME_COLUMN                Y

108          DROP_COLUMN                  Y

109          INCREASE_COLUMN_LENGTH       Y

110          DDL_IN_TRANSACTION           Y

111          DESCENDING_INDEXES           Y

112          SP_RENAME                    Y

113          REMOTE_SPROC                 Y

500          SYS_SPROC_VERSION            9.00.1399

(29 row(s) affected)