Sql collation issues when deploying live.

Question

History: I had a deployment the other night which involved executing a script that creates a stored procedure. However when running the script, an error occurred about the collation of the DB.

The Problem: With the help of our friend Google, I managed to find out that the problem I was having was because on our live environment's db has one collation and the temporary DB's collation is different, and I was getting an error when trying to insert into a variable table.

The Question: What is the best way forward, or my options?

Environment: sql 2000

Thanks in advance.

Answer 1

The obvious solution is to reinstall your development database with the same collation as the production database.

If you can't, for example if you have multiple production databases with different collations, you can make sure your updates either fail or succeed as a whole. Put a transaction around all the stored procedure updates, and make sure to use alter, not drop/create.

Answer 2

We have been running into this one with temporary databases that we use for integration tests, in relation to development and production databases. What we have done is to make sure that all databases use the same collation. In your case, you should probably be able to do a simple search/replace in the script file and simply change the collation to the one used in the target database.

Edit: changing in the script will get you forward now; in the longer run you will want to make sure to use the same collation in all related databases.

Answer 3

There are some solutions

• Make sure all your collate are matching in the same db. Else you'll be in for some pain.
• The right thing to do if your temp tables don't contain many data is to recreate them with the same collate as the dest tables.

• If that's not an option, look for string types, and force your collate to the destination db one

   INSERT INTO dest_db..DEST_TABLE ( DEST_VARCHAR_COLUMN )
   SELECT TMP_VARCHAR_COLUMN COLLATE <dest_db_collate>
   FROM tmp_db..#TEMP_TABLE