Changing database collation sql 2005

  • Hi all,

    I just have a question regarding changing the database collation after a database has been used for while.

    What is the best way to go about it and changing the master and then the user database.

    I know you can change the user db with the alter database... collate command.

    But i've heard that the objects will still hold the previous collation.

    Any ideas ?

    thx

  • Changing the master database collation is not easy. One of the steps is rebuilding the master database. All steps could be found here:

    http://msdn2.microsoft.com/en-us/library/ms179254.aspx

    When you use ALTER DATABASE...COLATE statement - only new objects will be created with the new collation. You can change existing objects collation using ALTER TABLE ... ALTER COLUMN .. COLLATE satement. But changing old objects collation has some limitations according to the BOL:

    "You cannot change the collation of a column that is currently referenced by any one of the following:

    A computed column

    An index

    Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

    A CHECK constraint

    A FOREIGN KEY constraint"

    There could be also problems if you rebuild only user database, but still have tempdb collation different.

    So, the cleanest way (but not the easiest) will be to rebuild master with the new collation and to recreate users databases as it described in the BOL:

    http://msdn2.microsoft.com/en-us/library/ms179254.aspx

  • Thanks for your reply...

    So basically I will have to backup and restore the database but reinstall 2005 first with the correct collation and then restore the database with the proper collation ( alter collate )

    Although the older objects in the restored database will retain in previous collation.

    Correct ?

    thx

  • correct as stated [will still keep existing column collations]. or ...

    1. script all current tables without explicit collation [and pref in dependency order]

    2. install SQL200x to get the instance to the collation that you want

    3. create an empty db with the correct db name with the desired [i.e. same as instance] collation

    4. fire your script to create tables with the desired [i.e. same as db] collation

    5. use DTS or SSIS to actually populate new db with data from existing one

    it is ugly to have mixed collation because any string comparison might need explicit collation

    - not self-contained to dodgydb as one frequently needs to use tempdb for temp/group/sorting

    HTH

    Dick

  • Thanks for that... it does indeed seem ugly and we will most probably keep existing collation for the present tables and all.

    I will let you know of any questions.

    Much appreciated

  • dbaker (4/14/2008)


    correct as stated [will still keep existing column collations]. or ...

    1. script all current tables without explicit collation [and pref in dependency order]

    2. install SQL200x to get the instance to the collation that you want

    3. create an empty db with the correct db name with the desired [i.e. same as instance] collation

    4. fire your script to create tables with the desired [i.e. same as db] collation

    5. use DTS or SSIS to actually populate new db with data from existing one

    it is ugly to have mixed collation because any string comparison might need explicit collation

    - not self-contained to dodgydb as one frequently needs to use tempdb for temp/group/sorting

    HTH

    Dick

    dbaker is correct and I have personally had to go through this with a db upgrade from SQL 2000 to SQL 2005. I went ahead and did the upgrade and a month later it was realized that the collation of the database was different from the requested collation of the new install (rookie move by me)...

    So it was decided I had to script everything out, and re-create all the objects for a new database with the correct collation and then use SSIS to do a simple import to all the tables...ect...

    Issue was that the import wizard kept on erroring out due to some sort of error with IDENTITY columns...

    I had to create a script that built ALL the insert statements for each table.

    The script analyzed each table to determine if it had an IDENTITY column and would handle it appropriately...

    Here is the script and you can use it if you like...

    /*

    -----------------------------------------------------------------------------------------

    This script was created when I experienced an issue with SQL 2005 and the

    IMPORT/EXPORT wizard which continually failed requiring manual intervention.

    -----------------------------------------------------------------------------------------

    Pre-STEPS

    ----------

    I had to re-create the database, logins, schemas...by scripting it out. The database

    was originally restored with the wrong collation

    SO I had to attempt to re-create the database by scripting it out with all the object

    level permissions, logins...ect.

    THEN IMPORT all the data to the new database tables...problem was the IMPORT wizard

    continued to fail with IDENTITY field errors even though the wizard was set to allow this...

    So this script gets around that...

    The script disables all triggers, then builds an INSERT INTO script

    based on the SOURCE and DESTINATION databases.

    In my example I renamed the current database something like DATABASE_OLD

    then created the new database with the correct name

    Ran the script to create the INSERT Statements

    Ran the script which loaded all the tables

    Verified the table counts

    -----------------------------------------------------------------------------------------

    */

    ------------------------------------------

    -- SQL 2005 Build Insert statement script

    ------------------------------------------

    SET NOCOUNT ON

    DECLARE @SQLCmd VARCHAR(MAX),

    @schemaid INT,

    @objectid INT,

    @is_identityINT,

    @loopcnt INT,

    @DestinationDB SYSNAME,

    @SourceDB SYSNAME

    ---------------------------------------------------------

    -- you need to enter the source and destination databases

    -- and make sure you are in the source database when the

    -- script is run.

    ---------------------------------------------------------

    SET @DestinationDB = ''

    SET @SourceDB = ''

    SET @loopcnt = 0

    ---------------------------------------------------------------------------------------

    -- create script to DISABLE all triggers for all tables within the destination database

    ---------------------------------------------------------------------------------------

    PRINT '-- Disable All Triggers'

    PRINT '-----------------------'

    SELECT'USE ' + @DestinationDB + CHAR(13) + CHAR(10) + 'GO'

    SELECT 'ALTER TABLE ' + @DestinationDB + '.dbo.' + tables.name + ' DISABLE TRIGGER ALL ' + CHAR(13) + CHAR(10)

    FROMsys.schemasschemas(NOLOCK)

    INNER JOIN sys.tables tables(NOLOCK) on (schemas.schema_id = tables.schema_id)

    WHEREtables.type = 'U'

    -------------------------------------------------------

    -- get a list of all the tables, schema_id, & object_id

    -------------------------------------------------------

    SELECT

    tables.schema_id,

    tables.name,

    tables.object_id,

    columns.is_identity

    INTO

    #InsertBuild

    FROM

    sys.tables tables(NOLOCK)

    INNER JOIN sys.columns columns(NOLOCK) on tables.object_id = columns.object_id

    WHERE

    schema_name(tables.schema_id) in ('dbo')AND

    tables.type = 'U'

    ORDER BY

    columns.is_identity DESC,

    tables.schema_id,

    tables.name,

    object_name(tables.object_id)

    ---------------------------------------------------------------------------------------------

    -- select inital table to be scripted out and assign the values to the schema_id & object_id.

    -- if the table has an identity field it will use the first sqlCmd which includes the

    -- IDENTITY_INSERT ON clause otherwise it will use a general INSERT statement.

    ---------------------------------------------------------------------------------------------

    SELECT TOP 1

    @schemaid = schema_id,

    @objectid = object_id,

    @is_identity = is_identity

    FROM #InsertBuild

    WHILE (SELECT count(*) FROM #InsertBuild) > 0

    BEGIN

    ------------------------------------------------

    -- figure out if the table has an identity field

    ------------------------------------------------

    IF @is_identity = 1

    BEGIN

    SELECT @SQLCmd ='SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' ON ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

    'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '(' + CHAR(13) + CHAR(10)

    FROM

    sys.schemasschemas

    INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

    WHERE

    schemas.schema_id = @schemaid and

    tables.object_id = @objectid

    END

    ELSE

    BEGIN

    SELECT@SQLCmd = 'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '(' + CHAR(13) + CHAR(10)

    FROM

    sys.schemasschemas

    INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

    WHERE

    schemas.schema_id = @schemaid and

    tables.object_id = @objectid

    END

    ---------------------------------------------------------------------------------------------------------------------------------------

    -- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement

    ---------------------------------------------------------------------------------------------------------------------------------------

    SELECT@SQLCmd= @SQLCmd + '[' + columns.name + '],' + CHAR(13) + CHAR(10)

    FROM

    sys.schemasschemas (NOLOCK)

    INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

    INNER JOIN sys.columns columns (NOLOCK) on (tables.object_id = columns.object_id)

    WHEREschemas.schema_id = @schemaid and

    tables.object_id = @objectid

    ORDER BY

    tables.name,

    columns.column_id

    --------------------------------------------------------------

    -- clips off the last comma at the end of the select statement

    --------------------------------------------------------------

    SET @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + ')' + CHAR(13) + CHAR(10) +

    'SELECT' + CHAR(13) + CHAR(10)

    ---------------------------------------------------------------------------------------------------------------------------------------

    -- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement

    ---------------------------------------------------------------------------------------------------------------------------------------

    SELECT@SQLCmd= @SQLCmd + '[' + columns.name + '],' + CHAR(13) + CHAR(10)

    FROMsys.schemasschemas(NOLOCK)

    INNER JOIN sys.tables tables(NOLOCK) on (schemas.schema_id = tables.schema_id)

    INNER JOIN sys.columnscolumns(NOLOCK) on (tables.object_id = columns.object_id)

    WHERE

    schemas.schema_id = @schemaidand

    tables.object_id = @objectid

    ORDER BY

    tables.name,

    columns.column_id

    --------------------------------------------------------------

    -- clips off the last comma at the end of the select statement

    --------------------------------------------------------------

    SET @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + CHAR(10)

    ----------------------------------------------------------------------------------------------

    -- identify the table name and turn off identity_insert option if table has an identity field

    ----------------------------------------------------------------------------------------------

    IF @is_identity = 1

    BEGIN

    SELECT @SQLCmd = @SQLCmd + 'FROM ' + @SourceDB + '.dbo.' + tables.name +CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

    'SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' OFF ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

    '--------------------------------------------------------------'

    FROMsys.schemasschemas(NOLOCK)

    INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

    WHERE

    schemas.schema_id = @schemaidand

    tables.object_id = @objectid

    END

    ELSE

    BEGIN

    SELECT @SQLCmd = @SQLCmd + 'FROM ' + @SourceDB + '.dbo.' + tables.name + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

    '--------------------------------------------------------------'

    FROMsys.schemasschemas(NOLOCK)

    INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

    WHERE

    schemas.schema_id = @schemaidand

    tables.object_id = @objectid

    END

    ------------------------------------------------------------

    -- print the statements that will be used to import the data

    ------------------------------------------------------------

    PRINT @SQLCmd

    -- EXEC (@SQLCmd)

    -- remove the current table from the temp table

    -----------------------------------------------

    DELETE FROM #InsertBuild

    WHERE schema_id = @schemaid and

    object_id = @objectid

    -- select the next table to be built

    ------------------------------------

    SELECT TOP 1

    @schemaid = schema_id,

    @objectid = object_id,

    @is_identity= is_identity

    FROM #InsertBuild

    END

    PRINT '-- Enable All Triggers'

    PRINT '----------------------'

    -- create script to enable all triggers for all tables within the destination database

    ---------------------------------------------------------------------------------------

    SELECT 'ALTER TABLE ' + @DestinationDB + '.dbo.' + tables.name + ' ENABLE TRIGGER ALL '

    FROM

    sys.schemasschemas

    INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

    WHEREtables.type = 'U'

    -- finished with the script, drop the temp table

    ------------------------------------------------

    DROP TABLE #InsertBuild

    SET NOCOUNT OFF

  • Thanks for all the great tips.

    I now have to determine the best way to proceed so that our application continues working well after any changes.

    I will write back with more questions .

  • How to change collation in which Partition views are used.

  • Hi

    This is a great article for Change collition sql server 2005 concern as DBA

    Regards

    Jayant Dass

    9650336531

    9313406257

  • This was an elegant solution to what has been a plague on our DBAs during migrations from old systems to new - particularly with COTS databases that routinely use character columns in foreign keys, et cetera. Thank you very much!

  • Are you referring to the script? If it worked glad it helped!

  • Hi Lee,

    am using your script to build the insert into statements for data export.. then when i run the generated script i see messages like..

    Msg 1088, Level 16, State 11, Line 3

    Cannot find the object "local.invdb.dbo.aero_videocard" because it does not exist or you do not have permissions.

    Have i used the correct syntax to specify the db server and database name for @DestinationDB.

    Here is how I specified now.. could you help me get it right ?

    SET @DestinationDB = 'servername.invdb'

    SET @SourceDB = 'local.invdb'

    SET @loopcnt = 0

    thanks,

    DK

  • Hi DK,

    The script that is posted in the first couple of replies has been changed by me and I have attached the updated script along with another script that you can utilize to compare row counts between the source and destination databases once you have completed the import from source to destination.

    I have updated the 'notes' section to hopefully outline how I personally have used the script...that may or may not answer any questions you have on how I intended the use of it when it was written.

    If you download it and have questions please let me know.

    Good luck, hope it works this time.

    Thanks,

    Lee

  • Thanks Lee. Will try the updated script.

  • Is there an update? Was the second script more successful?

Viewing 15 posts - 1 through 14 (of 14 total)

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