I migrated a database originally created in SQL Server 6.5 to 7 and then to SQL Server 2000. It has sort order 51 (case-sensitive, dictionary order). Now some stored procedures pop up a collation error in SQL Server 2000. I found out that this happens when temporary tables with varchar columns join permanent tables with SQL_Latin1_General_CP1_CS_AS added to each varchar by default. As soon as I add this collation to my temporary table, the error is gone.
My question is: Do I need to add collation to all the char/varchar columns of temporary tables in over 1000 stored procedures that I have in the database? Or is there some simpler way to solve the problem generally?
QUESTION POSED ON: 25 AUG 2005
QUESTION ANSWERED BY: Kevin Kline
If the server where you are running these stored procedures only supports this one database and application, then you can wish to set the default collation for the server as sort order 51. This will cause all tables created in TempDB to have the same sort order and collation as your own application tables. The bad news is that if your SQL Server has to support multiple databases with different sort orders and/or collations, you'll have to manually update all of the stored procedures. Note that when using the CREATE TEMPORARY TABLE statement, you can set the collation once for the entire table. You don't have to do it for each char/varchar column.
|
|
|
