Freigeben über

How to change the collation of all the existing objects at once inside a database after upgrading to SQL 2005?

  • Artikel

Consider the following the scenario

Scenario :

SQL Server 2000 Instance : Collation A

We perform a upgrade from SQL Server 2000 to SQL Server 2005 changing the collation.

After successful upgrade ,

SQL Server 2005 Instance : Collation B

2005 Databases : Collation B

Objects inside 2005 databases : Collation A 

Now this is what we would like to change to Collation B (New collation as that of new instance) as we had hundreds of such databases with thousands of objects.

Export/import will not work as it doesn’t preserve the identity column values. In other method , we couldn’t export primary keys.

Following are the steps which can be used to change the collation of all the existing objects

1. Script out the Source database for all objects using the "Generate Script" wizard (without collation) .

2. Create a NewDatabase (Destination db) and run the generated script .

3. BCP out the data from the source database. Run the below script on the source database to generate the BCP OUT commands for all objects. Copy paste the ouput to a batch file and run the batchfile .

select 'bcp.exe <SourceDatabaseName>.dbo.'+ name +' out c:\'+name+'.bak -S<SQLServerName\Instancename> -T -c -E' from sysobjects where xtype='U'

4. Once the BCP OUT is complete for all the tables, execute the below to generate BCP IN commands .

select 'bcp.exe <DestinationDatabaseName>.dbo.'+ name +' in c:\'+name+'.bak -S<SQLServerName\Instancename> -T -c -E' from sysobjects where xtype='U'

Copy and Paste the ouput to a batch file and run the batchfile. The identity values will be preserved as per the KB article

https://support.microsoft.com/?id=952111

P.S. : This is a workaround for a specific kind of scenario as mentioned above.

 

Prashant Kumar,
SE, Microsoft SQL Server

Comments

  • Anonymous
    February 19, 2009
    Can you please highlight when (in which steps above) the collation change really happened in the destination database? From steps above what I see is simple BCP OUT followed by BCP IN, may be I'm missing something

  • Anonymous
    February 19, 2009
    Hi Prasanna, Collation of the destination database changed soon after the successful upgrade. However objects inside the database were having older collation and the steps mentioned are to change the collation of objects . Please note I specified a new collation during upgrade . -- Prashant