Getting collation confict in IF statment and need help resolving it please
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.