Post reply

Change Collation using DTS

  • February 22, 2005 at 1:13 pm

    #74864

    Hi There, 

    I have an issue where I need to change the collation of a DB – I have 2 DB’s with different collation’s and have a requirement to make the collation the same for both DB’s. 

    Can this be done by using ‘Use Collation’ during the DTS procedure in SQL 2000?

     

     

    Thanks

  • February 22, 2005 at 10:14 pm

    #543606

    Wouldn't you just change the collation using the ALTER DATABASE statement since this seems to be a one time operation??

    Here is a MSDN reference for changing collations...

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_1pwz.asp

     

    Rushabh Mehta

    SQL Server MVP
    Solid Quality Learning

  • February 23, 2005 at 1:52 am

    #543630

    Changing collations using ALTER DATABASE requires that the database be in single user mode. So it is best to do it one time independently of the DTS.

    One things to remember : SQL SERVER 2000 allows to specify collation on a per column basis. If you change the database collation, check that all columns use the <defaut database collation>.

    HABIB.

    Kindest Regards,

    Habib Zmerli (MVP)

  • February 23, 2005 at 7:52 am

    #543703

    Changing the default collation for a database will only change the collation of the system tables. Any columns that have already been created will retain their collation. In fact it's sometimes useful to have tables with different collation to the database default (e.g. allows data to be case sensitive, but table and column names case insensitive). The neat thing about changing collations is that it doesn't actually change the data in the tables. It just changes how SQL Server reasons with it.

    Probably the easiest way to change the collation of all of the columns in your database is to generate the script for all tables using Enterprise Manager, find/replace all of the collation names then execute the DDL in a new database. Then use your favorite method to transfer data from old to new (e.g. insert/select, DTS, bcp).

    Another method is to code an alter table/alter column statement for each affected collumn (e.g. char, varchar). I don't particularly like this method because you have to include the datatype and null option as part of the statement.

    If anyone has an easier/quicker way to accomplish this on a large database (1000+ tables, many Gb data), I'm interested. I am not willing to consider 'allow updates' and manual change of collation value in syscolumns.

  • February 23, 2005 at 5:16 pm

    #543813

    Hi There,

    Thanks for all your reply's - they have been really useful.

    Regards

    Steve

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply