Thanks for the tip ... I'm a newbie to SQLSerevr.
You said - "...can have issues because you cannot compare the text
fields of different collations." -
Um ... It sounds as if all collations should be the same as temp (if you
want to compare text data - I did some googling to catch-up on this
common issue), which means that only one collation should be used per
instance - generally. That begs the question why would different
collations be allowed in the first place within an instance - User
beware I guess.
In your situation, will all your user databases be the same collation
after your change, or will you be multi collation across user databases?
TIA
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x25815828.[Email address protected]
Sent: Wednesday, January 31, 2007 8:08 AM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
IT IS A COMMON ISSUE when you have databases with collations different
from the server default. All work in tempdb(temptables, comparisons,
etc.) can have issues because you cannot compare the text fields of
different collations.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Mackey Glenn
[mailto:mssqldba-ezmlmshield-x93152553.[Email address protected]
Sent: Wednesday, January 31, 2007 7:56 AM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
Could you tell me why the tempdb has to have the same collation as the
user databases. What problems are you experiencing?
Do you have other user databases using different collation?
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x76268324.[Email address protected]
Sent: Tuesday, January 30, 2007 12:22 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
My users are having collation problems because all the databases are
have a collation different from the tempdb. Since I can't just change
collation for tempdb, I have to change server's collation. The only way
to do that seems to be by running rebuildm. I want to minimize the post
rebuild work, and was hoping that if I backup the master DB before the
rebuild, and then restore it after the rebuild, I will avoid having to
reattach DBs. I don't want master and tempdb to have a different
collation.
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x49218337.[Email address protected]
Sent: Tuesday, January 30, 2007 12:44 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
DON'T KNOW. Every time I have tried a rebuild it was as if you
reinstalled SQL and started over.
Why are you doing this? There is a reason the master, msdn, and tempdb
are the same collation. If tempdb was different you would begin getting
collation conflicts on all comparisons using temptables etc. I've seen
this when loading a database of a different collation on a server.
Unless maybe you were using completely Unicode and then it would not
matter what collation tempdb was.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x81271848.[Email address protected]
Sent: Tuesday, January 30, 2007 11:34 AM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
Thanks,
I was afraid so. Is it possible to backup master db, run rebuildm to
change its collation and then restore it (and MSDB I guess, because it
also gets rebuilt, right?) back so that I don't have to reattach?
Mordechai
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x60049643.[Email address protected]
Sent: Tuesday, January 30, 2007 12:15 PM
To: LazyDBA Discussion
Subject: RE: changing tempdb collation
I believe the server collation must change for any system databases to
change, and they all will be the same(master, msdb, tempdb, etc.)
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Mordechai Danielov
[mailto:mssqldba-ezmlmshield-x71819851.[Email address protected]
Sent: Tuesday, January 30, 2007 10:57 AM
To: LazyDBA Discussion
Subject: changing tempdb collation
Is there a a way to change tempdb collation, or do I have to change the
server collation for that?
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient(s) is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient(s) is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER:
This message and accompanying documents are covered by the Electronic
Communications Privacy Act, 18 U.S.C. 2510-2521, and contains
information intended for the specified individual(s) only. This
information is confidential. If you are not the intended recipient or an
agent responsible for delivering it to the intended recipient, you are
hereby notified that you have received this document in error and that
any review, dissemination, copying, or the taking of any action based on
the contents of this information is strictly prohibited. If you have
received this communication in error, please notify us immediately by
e-mail, and delete the original message.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended solely for the
person(s) or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient(s) is prohibited. If you received this in error, please
contact the sender and delete the material from any computer.
****************************************************************
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DISCLAIMER:
This message and accompanying documents are covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521, and contains information intended for the specified individual(s) only. This information is confidential. If you are not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, copying, or the taking of any action based on the contents of this information is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message.
MS Sql Server LazyDBA home page