How to change collation of msdb?

Question

How to change collation of msdb?

coder_t2

Ten Centuries

Points: 1224
More actions
June 9, 2010 at 12:03 pm

#236275

How do I change the collation of the msdb and tempdb databases? Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, tempdb is a copy of the model db every time the server is started, so changing the model db would change the temp db after a start and stop.

ALTER DATABASE model COLLATE SQL_Latin1_General_CP1_CI_AS

edit whoops can't change it in a straightforward fashion:

Msg 3708, Level 16, State 5, Line 1

Cannot alter the database 'model' because it is a system database.

just guessing, because everything i just googled said you have to rebuild and then reinstall all service packs, but maybe you could create a new blank database, change it's collation, back it up, then restore model from that backup?

i'd like to hear a reason why you'd want, or need, to change the msdb collation though.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

coder_t2 Ten Centuries Points: 1224 More actions · Answer 2

Well, I am trying to set up replication, but I get collation errors. My master database is a different collation than msdb, which is what I think is causing the problem. So I wanted to make all my databases the same collation. How do I rebuild? And do I really need to reinstall all service packs?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

tomiboy59 (6/9/2010)
Well, I am trying to set up replication, but I get collation errors.

Chances are there is a conflic in between replication source and replication target database collation.

If this is the case and I was working on replicating from A to B I would take database backup from A and restore it on top of B then create distributor/publisher/subscriptions.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

coder_t2 Ten Centuries Points: 1224 More actions · Answer 4

Well I am working on replicating from an Oracle database to my SQL Server database. How do I check the collation of the Oracle database to see if it is in fact different?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

tomiboy59 (6/9/2010)
Well I am working on replicating from an Oracle database to my SQL Server database.

That's a very different scenario, isn't it? 😀

Firstly ... ask you Oracle DBA what's the "character set" of your source Oracle database.

Secondly ... take into consideration that you can "localize" what Oracle is sending on the "client" side. In the Oracle world one thing is database encoding a.k.a. character set - and other very different thing is client "localization" which is handled by NLS_LANG.

Click here for details...http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543

Most likely you will have to set NLS_LANG on SQL Server side in a way that matches SQL Server collation.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

coder_t2 Ten Centuries Points: 1224 More actions · Answer 6

I'm skeptical that it is Oracle database that is giving me the issue. But I'm the newbie, so what do I know? lol. I'll talk to my Oracle DBA and see what he says. Also below is a link to a thread I started concerning this issue. It has pictures of the errors that I was getting.

http://www.dbforums.com/microsoft-sql-server/1657125-replication-collation-error.html

Hyperlinking doesn't appear to be working correctly, so I guess you'll have to copy and paste into your address bar.

coder_t2 Ten Centuries Points: 1224 More actions · Answer 7

Ok, well Language is American, and Character set is UTF8. It looks like this should work fine with my current collation. Am I wrong?