The odds are the collation you were using has been deprecated in SQL Server 2005 (search for “Limitations of the COLLATE Keyword”) and the applications that are failing didn’t implement error-handling around their collation specification code. Upgrading from SQL Server 2000 to 2005 does not change your server collation, even though the collation you’ve been using may have been deprecated. You should use :fn_helpcollations to see which collations are valid for your installation of SQL Server.

If you’re not sure what a collation is, or why you should care, read this MSDN article. Collation precedence tells SQL Server how to compare and otherwise work with the character string data types: char, varchar, text, nchar, nvarchar, and ntext. In many environments all database collations will match and nobody will have to concern themselves with collation at all. However, once 3rd party applications and/or internationally-scoped initiatives are undertaken, collation becomes important.

The following is a way you can work-around a deprecated collation in an inline T-SQL statement. If your SQL Server 2000 instance was using the collation Latin1_General_CP1_CS_AS, for example, SQL Server 2005 doesn’t know anything about that collation. If an application builds dynamic strings taking collation into consideration it might have code similar to this:


DECLARE @string nvarchar(1000)
SELECT @string=N'SELECT DB_NAME() COLLATE '+CONVERT(nvarchar(128),SERVERPROPERTY('Collation'))
EXEC dbo.sp_executesql @string


Now, if you try to execute the above on a SQL Server 2005 instance and your server’s collation is Latin1_General_CP1_CS_AS, the statement will fail (if you have a valid, non-deprecated collation this will work just fine). The next statement will work even if the collation has been deprecated because it uses a default:


DECLARE @string nvarchar(1000)
SELECT @string=N'SELECT DB_NAME() COLLATE '+CASE WHEN CONVERT(nvarchar(128),COLLATIONPROPERTY(CONVERT(nvarchar(128),SERVERPROPERTY('Collation')),'CodePage')) IS NULL THEN 'Latin1_General_CS_AS' ELSE CONVERT(nvarchar(128),SERVERPROPERTY('Collation')) END
EXEC dbo.sp_executesql @string


With this added code, even if your server’s collation has been deprecated, the default Latin1_General_CS_AS will be used. Obviously the default will necessarily be different in different environments, however this should be a good starting point toward getting a fix implemented.