Have Geek Brain

Sql sorting issues in realtime deployment

History: I had a deployment the other day that involved a script that creates a stored procedure. However, when running the script, there was an error in sorting the database.

Problem: With the help of our friend Google, I was able to figure out that the problem I ran into was that in our environment the live db has one collation and the DB temporary collation is different and I was getting an error when trying to insert into a variable table.

Question: What is the best way to promote or options?

Environment: sql 2000

Thanks in advance.

0


source to share


3 answers


We ran into this with temporary databases that we use for integration tests, in relation to development and production databases. We made it so that all databases use the same collation. In your case, you can probably do a simple search / replace in the script file and just change the mapping to the one used in the target database.



Edit: a change to the script will take you ahead; ultimately you will want to use the same collation across all related databases.

0


source


There are some solutions



  • Make sure all your mappings are the same in the same block. Otherwise, you will experience some pain.
  • The right thing to do if your temporary tables don't contain a lot of data is to recreate them with the same collation as the dest tables.

  • If that's not an option, look for row types and force your file to be assigned to db one

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


source


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 collation, you can ensure that your updates either didn't work or succeeded in general. Place a transaction around all updates to the stored procedure and make sure you are using alter and not drop / create.

-1


source






More articles:


All Articles