Getting collation confict in IF statment and need help resolving it please A SQL script I am writing is generating the error:


  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation.


I am not familiar with this error and don't know how to deal with it. I have been able to determine it is caused by the last IF check in the script as once I remove it the script runs fine. Can anyone help me figure out how to fix this issue? Here is my current code:

DECLARE @Source varchar(25);

SET @Source = 'DOD';
/*
Source options are:
    DOD
    SPRAGUEENERGY
    SRAPROD
*/

IF (@Source = 'DOD')
BEGIN
    USE DOD;

END

IF(@Source = 'SPRAGUEENERGY')
BEGIN
    USE SPRAGUEENERGY;

END

IF (@Source = 'SRAPROD')
BEGIN
    USE SRAPROD;
END
Well, that is interesting. I created your 3 databases using different collations for each database.  I tried starting from different databases to see the results.  Naturally I do not know which collations you used on each database, but apparently the SQL Server is (in fact) aware of this.


FIRST - SQL_Latin1_General_CP1_CI_AI   
DOD - SQL_Latin1_General_CP850_CI_AS  
SPRAGUEENERGY - SQL_Latin1_General_CP850_CI_AS  
SRAPROD - SQL_Latin1_General_CP1_CI_AI


Depending on the database I start with (I tried all 4) some give collation errors and some do not. So...  The answer is:

IF (@Source COLLATE DATABASE_DEFAULT = 'DBNAME' COLLATE DATABASE_DEFAULT)

This raises no errors no matter which database I start with.