[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Completely Change Database Collation

Hi all,

I have a situation where a SQL Server is being retired and all the databases moved over to another server. Up until the last database (!) all databases have gone across to the new server fine with no hiccups whatsoever, the last one however is causing problems becuase the collation of the database and all char/varchar etc fields in the database is different to the new server.

What I plan on doing is writing a script to cycle through all the tables in the database and change the collation of any char/varchar fields. Is this possible or is there a better way that anyone can suggest?

This is quite urgent so any suggestions are greatly appreciated!

Cheers,
Danny
0
LFMSupport
Asked:
LFMSupport
  • 20
  • 19
1 Solution
 
muzzy2003Commented:
I think you're on the right track here. I could suggest an approach that would be a simple edit of the system tables, but arbert would jump down my throat quicker than you can say "future compatibility issues".
0
 
muzzy2003Commented:
You could run this, replacing [xxx] with the collation you want - it generates a list of ALTER TABLE commands ...

SELECT      'ALTER TABLE [' + o.name + '] ALTER COLUMN [' + c.name + '] ' + t.name + CASE WHEN c.xtype = c.xusertype THEN '(' + CAST(c.prec AS nvarchar(10)) + ')' ELSE '' END + CASE WHEN c.isnullable = 0 THEN ' NOT' ELSE '' END + ' Null COLLATE [xx]'
FROM      sysobjects o
      INNER JOIN
            syscolumns c
            INNER JOIN
                  systypes t
                  ON c.xusertype = t.xusertype
            ON o.id = c.id
WHERE      c.collationid IS NOT Null
AND      o.xtype = 'U'
AND      o.name <> 'dtProperties'
0
 
LFMSupportAuthor Commented:
Thanks for the speedy response!

I'll give the script a go and let you know how it goes in a little while. Do you know if there are any issues with changing the table/field collation before changing the database collation or should I do that first?

Cheers,
Danny
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
muzzy2003Commented:
Don't think there are - doesn't really matter.
0
 
LFMSupportAuthor Commented:
Cool,

I'll give it a go and get back to you.
0
 
LFMSupportAuthor Commented:
After a bit of "tweaking" the script previously posted generates SQL Statements like the one below:

          ALTER TABLE [CB990023] ALTER COLUMN [CB_Cash_Account_Number_3] [char] (7)  COLLATE [SQL_Latin1_General_CP1_CI_AS]

When these statements are now run, every line generates the error:

          Server: Msg 170, Level 15, State 1, Line 13
          Line 13: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.

Am I missing something??

Danny
0
 
muzzy2003Commented:
You might need GO statements between them. Do they error when you run 1 line at a time?
0
 
LFMSupportAuthor Commented:
I tried adding the GO statement and running them individually. Both produced the error I posted earlier

Danny
0
 
muzzy2003Commented:
Replace this line:

Set rs = conn.Execute("SELECT SecRTEngApproval from TL_Users Where User = '" & userName & "'")

with this:

Set rs = conn.Execute("SELECT 3")

If this gives you a checkbox, then the problem is with the SQL. If this doesn't give you a checkbox, then the problem is somewhere else. I suspect the former.
0
 
muzzy2003Commented:
Sorry - wrong topic, too many windows open. Please ignore!!!!
0
 
LFMSupportAuthor Commented:
busy busy! :0)
0
 
muzzy2003Commented:
What we should be aiming for is:

ALTER TABLE [CB990023] ALTER COLUMN [CB_Cash_Account_Number_3] char(7) COLLATE SQL_Latin1_General_CP1_CI_AS Null (or NOT Null)

Can you tweak the [] to produce this, and reinstate the Null/NOT Null?
0
 
LFMSupportAuthor Commented:
I've re-set your original statement so the output is like this:

        ALTER TABLE [CB990023] ALTER COLUMN [CB_BT_CHG_Number] char(21) COLLATE [SQL_Latin1_General_CP1_CI_AS] NOT Null

I still get the same error. One thing to note, the "NOT Null" part of the line above is in a different place than the oringinal statements I was using. However, the result is the same no matter what.

Danny
0
 
muzzy2003Commented:
Can you remove the [] around the collation name? Yes, I know the NOT Null moved - my fault. The later version is right.
0
 
LFMSupportAuthor Commented:
The error message I now receive is:

Server: Msg 5074, Level 16, State 8, Line 14
The index 'AK1SY03400' is dependent on column 'USERID'.
Server: Msg 4922, Level 16, State 1, Line 14
ALTER TABLE ALTER COLUMN USERID failed because one or more objects access this column.

Do I need to change the collation on all indexes too??
0
 
muzzy2003Commented:
Oh damn. No, you'll need to drop them and rebuild them. There was a recent question on how to do quickly and easily in this same topic area. I'll try to find it for you.
0
 
LFMSupportAuthor Commented:
So the process will be
  - Drop the indexes
  - Change the Collation
  - Recreate the indexes

Danny
0
 
muzzy2003Commented:
Yes, correct. The sort order on which the indexes are constructed is determined by the collation, that's why you need to do it.
0
 
LFMSupportAuthor Commented:
Did you manage to find the post about quickly dropping and recreate the indexes?

Danny
0
 
muzzy2003Commented:
Not yet, sorry. Been offline most of the time since that post. Am looking now.
0
 
LFMSupportAuthor Commented:
Is this the post you were thinking of?

       http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20522825.html


Danny
0
 
LFMSupportAuthor Commented:
Based on your answer in: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21193750.html

I've amended the orginal query posted here to output this:

   EXEC usp_DropIndexes [SY03400]
   ALTER TABLE [SY03400] ALTER COLUMN [USERID] char(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT Null
   EXEC usp_RecreateIndexesSY03400

The statements above return the same error as before, I take it I'm missing something!!??

Cheers,
Danny
0
 
muzzy2003Commented:
Try EXEC usp_DropIndexes 'SY03400' - i.e. skip the []. Any better?
0
 
LFMSupportAuthor Commented:
Same problem I'm afraid.

If I run "EXEC usp_DropIndexes 'SY03400'" on it's own both with and without single quotes and/or square brackets around the table name then the command runs fine, the problem only occurs when the "ALTER TABLE.." statement runs.

Thanks for your patience!

Danny
0
 
muzzy2003Commented:
The primary key doesn't include a character field on this table does it? My script won't touch primary keys.
0
 
LFMSupportAuthor Commented:
The primary key is an int field called DEX_ROW_ID

Danny
0
 
muzzy2003Commented:
What about a unique index of some sort? I might have also excluded those.
0
 
LFMSupportAuthor Commented:
This is the create statement for the index so I guess it is unique. Is there a way around it?

    CREATE  UNIQUE  INDEX [AK1SY03400] ON [dbo].[SY03400]([USERID], [FILENAME], [INDXLONG], [DEX_ROW_ID]) ON [PRIMARY]

Danny
0
 
muzzy2003Commented:
Yes, you can just drop and recreate this one manually. My script was designed not to drop UNIQUE indexes, and if you only have this one to deal with then this will be easier to do manually than altering the script.
0
 
LFMSupportAuthor Commented:
Are there any common nasty consequences associatted with dropping UNIQUE indexes?

Danny
0
 
muzzy2003Commented:
No. The only thing that might happen when you recreate it under a different collation is that there might be values that are considered duplicates under that new collation. For instance, if you change collation from Latin1_General_CI_AS to Latin1_General_CI_AI, then the values "Premiere" and "Première", which previously would have been considered different, will now be considered the same. This would mean that the new unique index won't create. To check if this is the case, try:

SELECT USERID COLLATE <new collation> FILENAME, INDXLONG, DEX_ROW_ID, COUNT(*)
FROM SY03400
GROUP BY USERID COLLATE <new collation> FILENAME, INDXLONG, DEX_ROW_ID
HAVING COUNT(*) > 1

If no rows are returned, your new unique index under the new collation should create with no problems at all. You can run this with all the indexes in place as a little preliminary test.
0
 
muzzy2003Commented:
Sorry - need a comma after the new collation in the two places in that last bit of SQL.
0
 
LFMSupportAuthor Commented:
Cool, I'll run that little test and see how it goes.

Danny
0
 
LFMSupportAuthor Commented:
That test you posted came back good so I should be ok to drop the unique indexes.

If I wanted to add unique indexes to the query in that post you pointed me at what would i need to change?
0
 
muzzy2003Commented:
It would probably be something in the filter:

          AND     status & 0x800 = 0
          AND     status & 0x2 = 0
          AND     status <> 0

in the cursor definition, but off the top of my head I'm not sure what exactly.
0
 
LFMSupportAuthor Commented:
I'll spend a bit of time on it today and have a bash at it.

Thanks very much for your help and patience.

Danny
0
 
LFMSupportAuthor Commented:
Sorry to ask about this after the answer was accepted but I have a little query!

What does have the "& 0x800" and "& 0x2" do in the where statement you posted? I've never seen that before.

Danny
0
 
muzzy2003Commented:
status is a combination of bit flags - status & 0x800 = 0 means status doesn't have bit 11 set (0x800 = 2 ^ 11), and status & 0x2 = 0 means status doesn't have bit 1 set (0x2 = 2 ^ 1). The meanings of these two bits are something like unique index and primary key, but I can't remember exactly off the top of my head. Look up sysindexes in Books Online for a complete list of these status flags.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 20
  • 19
Tackle projects and never again get stuck behind a technical roadblock.
Join Now