| Author |
Topic |
|
TonyTheDBA
Posting Yak Master
121 Posts |
 Posted - 2007-10-12 : 09:45:45
|
| OK Picture this, Two systems supplied by the same supplier, residing on two different SQL Servers. In order to Consolidate the use of Hardware (We've put Server two into a SQL Cluster with bigger boxes :) ) we are moving the DB's from Server1 to Server2. The supplier will take responsibility for upgrading the application, its our job to move the DB's (My fault I made the mistake of assuing it was simply a case of BAckup up on Server1 copying the BAckups and restoring on server two! That bit works well . . . . )Yeah, now the RUB The two servers have different collations!OK Simple, USE ALTER DATABASE DB1 COLLATE SQL_Latin1_General_CP1_CI_ASAh but of course that then leaves all the char fields with the old collation. Looks as though I have to Script all the indices, drop the indices, change the Collation for each field in each table in each db, then recreate the indices.Correct??Any tools out there to help me do it?Otherwise I'm facing the task of writig a script to create the Alter Table Alter COlumn Statements, and then to script each index.I'm wondering if it would be easier to use DTS to do it?ARRRGGGGHHHHH! Suppliers!-- RegardsTony The DBA |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 09:52:49
|
Why change the collation of the DB?Its only queries that create Temp Tables WITHOUT specifying COLLATE statement, and cross-database query conditions (JOIN clause and WHERE) that will need explicit COLLATE.And as you said:"The supplier will take responsibility for upgrading the application"So they'll have to fix their sh1te, right?  Kristen |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-13 : 01:02:26
|
| If you have to change db collation, need to create db and all objects with new collation, bcp out data from source db then bcp them into new db. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-14 : 03:32:15
|
| Tony, I guess that can be done using just one queryYou do something like (sorry I dont havew a lot of time now, but you can see the diea)select 'ALTER TABLE ['+O.name+'] ALTER COLUMN ['+C.name+'] COLLATION your_collation'from sysobjects O, syscolumns Cwhere O.type='U' -- tablesand O.id=C.idand C.type (or xtype???) IN (dont remember type ids for char types)Then you copy/paste the result and execute it :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 05:26:10
|
| OK, I guess I'm missing something here. Is it important to change the collation?Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-14 : 16:55:28
|
| Changing collation is easy but not let existing data work in new collation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-14 : 18:07:56
|
| Indeed; so why not leave the collation as it is? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-14 : 20:49:50
|
| Agree unless the app breaks without changing collation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-15 : 03:44:12
|
We're covered the rmiao The OP said that"The supplier will take responsibility for upgrading the application" |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-10-15 : 07:19:40
|
| Hi Guy's Thanks for the replies.When I said the supplier will take responsibility for upgradin the App, what I meant was changing the app so that it points to the new db :(. We can get then to do it for us . . . . at a cost :(, and even better is that these servers were installed before I, or any other local DBA, was in post, and the suppliers instaleld SQL on both boxes!)Why change the collation, well probably because thesupplier uses lots of temporary tables and cross database joins!!! (The fools, the fools).Actually, I've been looking at the server that we are consolidating onto and we have enough resource available to install a second instance with the correct collation, and looking at this option more closely (after thinking about it all weekend, who'd be a DBA ?) it could be the better option as it keeps both of the environments (from a security POV) separate. This raises another question, When installing a new instance of SQL Server onto a Cluster am I likely to take the default instance down? I know it happens when you apply SP4 to an instance, but IIRC each instance is independant of each other.-- RegardsTony The DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-15 : 08:43:10
|
| "I meant was changing the app so that it points to the new db"They need a rocket stuck up their USB port!The Instance thing sounds OK. Who knows what unexpected nockk-on-side-effects you might get from a collation change to all your existing data ...Can't help with your last point, sorry.Kristen |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2007-10-15 : 09:06:39
|
quote:
They need a rocket stuck up their USB port!
Oh they need so much more than that, problem being that they are supplying the public sector, so think they can get away with anything they please, (They've had a hard time of it here recently :) )I'm going to try an install on our Virtual Cluster and see what happens.-- RegardsTony The DBA |
|
|
|
|
Different Collation Sequences on DBs