Server and Database Collation Questions
-
I have a number of collation questions because of when we rebuilt a server yesterday we used the wrong collation. It wouldn't have mattered as we figured it would be fixed once we restored the master from a backup. Then it turned out all our master backup files were bad.
A question I have now is that when you restore a master from a backup does that change the collation to whatever it was at the time the backup was created? Or will the server keep the collation you chose at the time of setup or when you rebuilt the master?
Is there a way to change the default server collation for SQL Server 2000 without rebuilding the master database?
If I remember correctly the model database is used as the model for each new database you create on a server. If that is true, then is there a way to change the collation on the model database so that any database you create will get that collation and not get the collation for the entire server?
Anyone know a way to change the collation on a column for a table that is being replicated?
Thanks for reading and even if you only know the answer to one of my questions, please post as it will help me.
Thanks
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
1. If you restore the master, it should keep the collation that it had previously.
2. In order to change the instance collation, you must rebuild the master database (as far as I know).
3. The ALTER DATABASE command allows you to change the collation at the database level.
4. Replicated column colation ???? I do not know this one.
-
just to add to John Rowan's reply :
- With alter database, only new created objects will use the new collation ! Existing objects will still use the old collation !
- regarding modeldb :
to replace it with one of the collation of your choice, you'll need mdf/ldf files from a model-db with that collation and follow the "move model db" procedure. (This will need your server to be started with special options !)
- Altough in theory mixing collations is possible, I don't advise to use it unless you have no other choice. I'd suggest you reinstall the server. (model, msdb, tempdb are use the server collation you specified at server-install-time)
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
-
here's a script i've used to generate the SQLs needed to change collation of a database for the existing items alzdba mentioned above;
I wouldn't try to modify this to fiddle with the master database, but for other databases this has worked well for me.
Note i limited the results to TOP 5 because this SQL can be kind of long running; you'd want to take out the top statement if you were going to use this:
SELECT 'alter database ' + db_name() + ' COLLATE SQL_Latin1_General_CP1_CI_AS'
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE) + '(' + CONVERT(VARCHAR,SYSCOLUMNS.LENGTH) + ') '
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('char','varchar', 'nchar','nvarchar')
ORDER BY TBLNAME,COLNAME
SELECT TOP 5
'ALTER TABLE ' + SYSOBJECTS.NAME + ' ALTER COLUMN ' + SYSCOLUMNS.NAME + ' '
+ TYPE_NAME(SYSCOLUMNS.XTYPE)
+ ' COLLATE SQL_Latin1_General_CP1_CI_AS'
+ CASE ISNULLABLE WHEN 0 THEN ' NOT NULL' WHEN 1 THEN ' NULL' END AS ALTERSTMT,
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE,
SYSCOLUMNS.LENGTH as length
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('text','ntext')
ORDER BY TBLNAME,COLNAME
typical results:
alter database AZGMVB COLLATE SQL_Latin1_General_CP1_CI_AS ALTER TABLE CacheData ALTER COLUMN Key varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CacheData Key varchar 128 ALTER TABLE CacheData ALTER COLUMN PartitionName varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CacheData PartitionName varchar 128 ALTER TABLE CENSUSTRACTS ALTER COLUMN CENSUSTRACT varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CENSUSTRACTS CENSUSTRACT varchar 10 ALTER TABLE CENSUSTRACTS ALTER COLUMN COUNTYFIPS varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CENSUSTRACTS COUNTYFIPS varchar 3 ALTER TABLE CENSUSTRACTS ALTER COLUMN STATECODE varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CENSUSTRACTS STATECODE varchar 2 ALTER TABLE AGENCY_WEB_USER_AGREEMENT ALTER COLUMN AGREEMENT_BODY text COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL AGENCY_WEB_USER_AGREEMENT AGREEMENT_BODY text 16 ALTER TABLE CMCONT ALTER COLUMN NOTES text COLLATE SQL_Latin1_General_CP1_CI_AS NULL CMCONT NOTES text 16 ALTER TABLE CMCONTACT ALTER COLUMN COMMENTS text COLLATE SQL_Latin1_General_CP1_CI_AS NULL CMCONTACT COMMENTS text 16 ALTER TABLE CMENTITY ALTER COLUMN COMMENTS text COLLATE SQL_Latin1_General_CP1_CI_AS NULL CMENTITY COMMENTS text 16 ALTER TABLE COMPCOMMENTS ALTER COLUMN RCOMMENT text COLLATE SQL_Latin1_General_CP1_CI_AS NULL COMPCOMMENTS RCOMMENT text 16 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! -
Thanks for all your replies.
The problem is this server crashed a couple days and because we were told to get it up as soon as possible all the checks that should have been done were not and so we have a server with a collation other than what all our other servers have. So far I still have not been told exactly what the problem was and because of the problems we had rebuilding the master I don't want to mess with it again and where I work now they have become very intolerant of anything going wrong in our production environment. My worry is that the rebuild will force us to redo replication which they won't let us do during working hours and then that messes up night time processes and everything cascades from there.
So I am trying to learn what alternatives there are if any.
The tables that need to have the collation changed are tables that are being replicated so the ALTER TABLE command won't work there. Our work around so far is to CAST(columnname as varchar(10)) COLLATE collationname. We have put this in one SP that was doing a UNION between two tables with different collations.
I've looked in BOL and haven't found anything else so I posted these questions to see if I just wasn't finding things I could do.
I'll look into the model db a bit more since that will help for any new databases we create on that server.
Once again thanks and if anyone else has any ideas please post them here.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply