Sunday, March 9, 2008

Change collation in tempdb

What's happening if you installed SQL Server instance with a collation that is different from a database collation? We just started testing our production application and everything seem to work well, however one of our stored procedure inserts hebrew characters into temporary table and then after some operations the data get insertded into a real table. Guess what wee have seen in the database? Right,we have seen '????' symbols. Sure, if you do not use temporary table and insert the data directly into a permanent table you will see the right characters. Someone said that we should decline of using temporary table and insert the data into 'temporary' permanent table. Another guy said that we should run ALTER DATABASE tempdb command to change COLLATION, but as we know you cannot run this statement on system databases.
The error is
Msg 3708, Level 16, State 5, Line 1
Cannot alter the database 'tempdb' because it is a system database.

So what is the solution? Well , use the REBUILDDATABASE option in Setup.exe or re-install the instance. Fortunately, the whole story happened on developing machine and we did not forget to install PRODUCTION server with right collation:-).

Just wanted to note you how important is to choose the 'right' collation while installing production server.

2 comments:

men's clothing said...

I feel honored and privileged after reading your posts.You know,summer is coming!
hommes chaussures
Chaussures Homme Nike,hommes chaussures pas cher.fashionable homme chaussure,Best wishes to you!
Acheter hommes chaussures in our shop

Unknown said...

great article, I was very impressed about it, wish you would have stayed next share
juegos.com
juegos de matar
jogos friv