March 17, 2008 at 2:15 pm
It seems the best way to accomplish setting a new SQL instance default collation is to reinstall SQL. I can detach all my nonsystem databases, and MSDB too I think, then re-attach. But what happens to everything in Master ?? How do I get all my logins ?? What else do I need to watch for ?? Do I need to script everything out & the re-create with the scripts ??
Any firsthand experience out there ?
March 17, 2008 at 2:58 pm
We prefer having single collation server instances !
The impact a instance level is a reinstall in our case.
Mixed collation databases may cause tempdb to have some hickups because
of collation conflicts
e.g.
db default collation is Latin1_General_CS_AS
server has been installed Latin1_General_CI_AI (so tempdb uses this)
create table #tmp (col1 char(10) )
insert into #tmp values ('ABC')
select *
from yourtable T1
inner join #tmp t
on T1.thename = t.col1 -- confilicts ?
you'll need to convert to common collation
declare @teststr varchar(100)
declare @comparechar char(1)
select @teststr = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$abcdefghijklmnopqrstuvwxyz%^&*()+=|.{[}]<>?/:"-~'';' --COLLATE Latin1_General_CS_AI
select @comparechar = 'k' -- COLLATE Latin1_General_CS_AI
--print CHARINDEX('k',@teststr)
print CHARINDEX(@comparechar COLLATE Latin1_General_CS_AI ,@teststr COLLATE Latin1_General_CS_AI )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2008 at 3:34 pm
Use the Rebuild Wizard (Rebuildm.exe), located in the \Program Files\Microsoft SQL Server\80\Tools\BINN directory. This can be used to pick a new collation. In any case you still need to script out the logins. There are several scripts on this site to do this. It will likely be something like:
SELECT 'EXEC sp_addlogin @loginame = ''' + l.loginname + ''''
,', @defdb = ''' + l.dbname + ''''
,', @deflanguage = ''' + l.language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(l.password AS varbinary(256))
,', @sid ='
, l.sid
FROM syslogins l
Save the output. You will need to run it again after master has been rebuilt
Francis
March 19, 2008 at 5:46 pm
After reinstalling SQL, the system databases will have the new collation, but msdb will be empty (no jobs or DTS). If msdb is restored from a backup, then it will get set back to the old collation, right ?? So is there no use for the msdb backup. Just restore the jobs from scripts ?
March 20, 2008 at 7:39 am
You are right. Script out the jobs and then add them back after the rebuild.
Francis
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply