Q
Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedures pop up a collation error in SQL Server 2000

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?

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?

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.
This was last published in August 2005

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close