SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

This document is created by Brian Cidern, he has written this excellent extension to SQL Expert who SQL SERVER – Change Collation of Database Column – T-SQL Script. His scripts are not only extremely helpful to achieve the task of consolidating collations in quick script. His script not only works perfectly but excellent piece of code and logic.

Hats off to you Brian! You can reach Brian at his email address (brians.sql.blog (at) gmail (dot) com) or leave comment here.

Download all scripts and explanation here

About Collation Consolidation

At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.  I’m sure there are as many reasons as to how you’ve come to this juncture as there are readers.  Maybe you have DBA’s and instances around the world and the local staff are using their own collations.  Or, maybe you’ve learned the hard way that ALTER DATABASE x SET COLLATION y doesn’t traverse.  Whatever it is, you’re probably here for the one underlying reason.  Manually consolidating collations would be a nightmare.  So, without loading to much fluff, let’s jump right in.

Here is the basic approach:

  1. Identify all the columns which have incorrect collation values.
  2. Identify all objects bound to above columns, including indexes, keys, checks and UDF’s,
  3. Script out all of the above object DDL.
  4. Drop all of the objects.
  5. Update all the columns with the correct collation
  6. Recreate all of the dropped objects using the DDL generated in step 3

This is done with a collection of non-destructive scripts which generate the DDL to perform all of the above.  This generated DDL MUST be saved to scripts for later use on the target databases.  And obviously, you’ll need one set of scripts per database.  If that requires clarification, just stop reading now.

In this article, I give to you the four scripts to do all this and will try to provide as much understanding as possible.  The first script is purely to create a stored procedure which will be repeatedly called.  This proc will need to exist in a database named “DBADB”.  If you want to use a different database, you’ll need to do a search and replace in the first script and the last script since the database name is hard-coded.  I tried to make these somewhat generic, but the only approach that came to mind would be to use OLE to invoke the VBScript engine to prompt you for the name of the database where this stored procedure is located.  Overkill in my opinion.  Ctrl-H is your friend here.

The script to perform step 3 above uses DMO via OLE, so if you’ve disabled the extended stored procedures; sp_OACreate, sp_OASetProperty, sp_OAMethod, etc, you won’t be able to continue until they’ve been enabled.

One final note.  If you’re not a member of the System Administrators role, please don’t try to execute these.  You’ll probably find your account revoked quickly.

Ok, time to roll up your sleeves.

Script 1 – The DDL Generator

This first script is to create a stored procedure.  This procedure must reside in a location that will not interfere with other databases, whether they’re development, test or production.  The SP uses DMO via OLE to capture a buffer dump of the object code.  The result is the same as if you right-clicked an object and selected “Script to new window as…”

The DMO hierarchy of a SQL instance is weird.  Object reference isn’t particularly intuitive.  But, for the most part, objects are contained within the associated table collection.  In DBA language, that would look like

Table 1:M Checks

Table 1:M Keys

Table 1:M Indexes

Database 1:M UserDefinedFunctions

Therefore, to locate a particular object in the DMO hierarchy, you would need to pass in all the required parameters, including the object type.  So, for checks, keys and indexes, you must also pass in the database name and the parent table name.  For functions, only the database name.

sp_scriptObject(

@in_dbName SysName,

@in_objType VarChar(100),

@in_objName VarChar(100),

@in_objSubName VarChar(100) = NULL

)

Now, the good news.  The subsequent scripts will do all of the above automatically, and this script will be called repeatedly.

Implementing this script.

As I mentioned, this script will be called repeatedly.  The script from whence it’s called will need to be modified to make reference to the correct database name.  These scripts have been designed to work with a DBA-use only database called “DBADB”.  Therefore, if you choose to keep this procedure located in a database of a different name, you’ll need to perform a search and replace for all the “DBADB” reference.  Otherwise, you will need to create a database named “DBADB”.

Execute the first script.  This will create the necessary stored procedure.

Script 2 – Drop Dependencies

The name for this script is somewhat deceiving.  It is a non-destructive script and only performs a set of SELECT statements.  Rather, this script generates a collection of DROP [object] statements.

The goal here is to identify all the objects that are bound to columns which have incorrect collation settings.  There is no clean way to pull them all back in a single statement since determining this is handled differently for the various types of objects.  So, they’re all concatenated together with UNION’s.  The orders in which these are executed are as follows:

  1. Table Functions
  2. Unique Constraints
  3. Check Constraints
  4. Indexes

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

It’s performed in this order in attempt to avoid dependency issues.

The result is a collection of DROP statements.  This must then be copy and pasted into a script and saved for later use.  Best to use a naming convention that reflects the name of the database, and that this will be the first script to execute when you’re ready to begin the patching, for example: myDatabase_post-script_1.sql, myDatabase_post-script_2.sql, etc.

You should take note, it is possible that some databases may not have any objects that are bound to columns that need to be updated, so it’s perfectly fine if this script produces no results.

Script 3 – Change Collations

Much like script 2, this script is also non-destructive and will produce a collection of DDL statements to update the column collations.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

VERY IMPORTANT.  These scripts were originally intended to allow us to consolidate all our collations in a SQL 2000 to SQL 2008 migration.  Therefore, this script WILL produce two lines of code that you may or may not want.

ALTER DATABASE ‘ + DB_NAME() + ‘ SET COMPATIBILITY_LEVEL=100

and

ALTER DATABASE ‘ + DB_NAME() + ‘ COLLATE ‘ + @collation

where @collation is a variable defined at the beginning of the script.

You can alter the original script I’ve provided, or you can simply remove them from the output before saving.

The remaining results will be a collection of statements to alter the column collations:

ALTER TABLE [tbl] ALTER COLUMN [clm] column_spec() COLLATE [new_collation]

Once this has been executed, copy and paste the output into another script file for later use (myDatabase_post-script_2.sql)

Script 4 – Create Dependent Objects

This is where most of the work takes place.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

Open the script, connect to the desired database and hit the [fire] button.  Again, like those previous, this script is non-destructive, and produces a series of DDL statements.

The first you’ll notice in the output is the “USE DBADB” statement.  It is very important to note here that if the stored procedure (sp_scriptObject) from the very first script is stored in another database, that this is reflected in the output.

The remainder of the output looks like:

EXEC DBADB.dbo.sp_scriptObject…

With all of the required parameters, based on the type and location of the object.  The other thing you may notice here is that the order in which these statements are in, is the exact reverse of the order from the 2nd script; again, with the intent to avoid dependency crashes.

This output now needs to be copy and pasted to a new query window – not to a file.  The output of the new window should be set to text (Query > Results in Text) or (Ctrl-T).  Execute the statements.  Be aware, that this could take a significant amount of time based on the number of objects and how complex they are.

But, in essence, you will be trapping a buffer dump for each object that needs to be dropped.

VERY IMPORTANT.

There may be a case where an object’s DDL is simply too large.  There are limitations in which the VBScript engine stores string data.  I can’t recall exactly what the issue was, nor do I care to look it up again. But basically, the buffer is limited to 8k.  If your object DDL is greater than 8k, an error will be thrown and the object DDL will NOT be trapped.

After this script has finished, you must first scan the output for errors.  If any are found, identify the object which was not scripted and manually script those and paste them into the output.

This output must then be saved to a final script for execution against the target database.

Final Step

In the end, the above work will have produced 3 scripts that now need to be run against the target database.  Now is a good time to clarify that these generated scripts are very destructive.  So, please take your time, test, test and test.  If you choose to do this on a production box, your career could be at stake.  There is never one-size-fits-all solution, so for each scenario, these scripts may required modifications.  These were produced to fix my needs, but I did try to keep them as generic as possible for re-use.

On your target database, execute each of the generated scripts, obviously in the order specified.

The net result will leave your database with each column, along with the database set at your desired collation setting.

The Quick and Dirty Approach

This section is for those who don’t really care to know the details and simply provides an exact step-by-step.

  1. Backup the target database.
  2. Copy the 4 scripts to an empty folder.
  3. Open Pre-Script_1_DROP_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  4. Open Pre-Script_2_CHANGE_COLLATIONS.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  5. Open Pre-Script_3_CREATE_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  6. Create a database named “DBADB”
  7. Execute Pre-Script_0_DBADB.sp_scriptObject.sql
  8. Change the working database to the one which requires collation changes.
  9. Execute Pre-Script_1_DROP_DEPENDENCIES.sql
  10. Save the output to a text file named “dbName_post-script_1.sql”
  11. Execute Pre-Script_2_CHANGE_COLLATIONS.sql
  12. Save the output to a text file named “dbName_post-script_2.sql”
  13. Execute Pre-Script_3_CREATE_DEPENDENCIES.sql
  14. Copy the output to a new query window and execute.
  15. Scan the secondary output for errors.  Identify any failed objects and manually script out the DDL.  Paste it into the location where the error occurred.
  16. Now, save this output to a text file named “dbName_post-script_3.sql”
  17. Copy dbName_post-script_1.sql, dbName_post-script_2.sql, and dbName_post-script_3.sql to the target machine.
  18. Connect to the desired target database and execute each of the above post-scripts in numeric order.

Good luck.  Please heed any warnings I may have mentioned.  If you have any questions, certainly send me an email and I will try to answer promptly.  But, also please be patient, I do have a full time job that takes priority.

Brian Cidern
brians.sql.blog (at) gmail (dot) com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

29 thoughts on “SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

  1. I let it work on MSSQL 2008.

    One remark, I had to run sp_configure to enable ‘Ole Automation Procedures’ during generation of the CREATE script.

  2. I tried it on sql server 2005. the first two scripts ran find, but when I ran the third script, Pre-Script_3_CREATE_DEPENDENCIES.sql, I kept getting

    ERROR: Connect for each call.

    I am sysadm, and do not know what is wrong. I need to have something by friday, and am researching another method that does about the same thing, but is more manual, and is taking much longer. any suggestions as to what I am doing wrong would be appreciated.

  3. Thx,

    This was a very helpfull article and scripts.

    There are 2 errors
    1: in de Usage.txt it says you need to create the database named ‘DBA_Data’ wich should be DBADB (as mentioned in the article)
    2: when you use these scripts in 2005, you have to change
    Pre-Script_2_CHANGE_COLLATIONS:
    change
    FROM INFORMATION_SCHEMA.columns
    into
    FROM INFORMATION_SCHEMA.COLUMNS

    After this it works like a charm.

  4. Hello all.
    This is the author of the article and have been meaning to publish a couple bugs I’ve discovered.

    Script: Pre-Script_0_DBADB.sp_scriptObject.sql
    Line 81
    Should be changed from:
    EXEC @rc = sp_OAMethod @o_server , ‘Connect’, NULL, ‘(local)’

    to

    EXEC @rc = sp_OAMethod @o_server , ‘Connect’, NULL, @@SERVERNAME

    The above corrects the “connect” issue when running the scripts against a named instance.

    These scripts were technically designed to run on a SQL 2000 instance, where the generated DDL could then be run on any version. So, technically this isn’t an error. However, I’ve found that these scripts do run fine on SQL’05 and SQL’08. However, Microsoft dropped DMO in 2005 and replaced it with SMO. So, in order for these to run, you may need to install DMO with the Backward Compatability package which can be downloaded at:

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

    @CJ – Thanks for spotting that. Sounds like you’re current collation is Case Sensitive. So, by correcting the case for .COLUMNS, it makes the script much more generic – exactly what I was trying to achieve.

    @Kevin – Apologies for not posting this sooner. Your connection issue is addressed in my first bullet point above.

  5. Hi,

    The database I need to convert used alot of user defined types, to support this I changed the select in script 2 as follows. Basically it checks the DOMAIN_NAME which on my system contained the name of the user defined type for the column. (User defined types appear to take the default collation of the database so this is not specified)
    ——-

    SELECT [SET_COLLATION] = (‘ALTER TABLE [‘ + TABLE_NAME + ‘] ALTER COLUMN [‘ + COLUMN_NAME + ‘] ‘ +
    CASE WHEN DOMAIN_NAME is null THEN
    DATA_TYPE +
    CASE WHEN
    DATA_TYPE IN (‘text’,’ntext’) THEN
    ‘ COLLATE ‘ + @collation
    ELSE
    ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))+’)’ + ‘ COLLATE ‘ + @collation
    END
    ELSE
    DOMAIN_NAME
    END
    + CASE IS_NULLABLE WHEN ‘NO’ THEN ‘ NOT NULL’ WHEN ‘YES’ THEN ‘ NULL’ END

    )
    COLLATE Latin1_General_CI_AS
    FROM INFORMATION_SCHEMA.columns
    WHERE
    TABLE_NAME N’dtproperties’ AND
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N’IsUserTable’) = 1 AND
    COLLATION_NAME @collation

  6. Hi

    Another issue I has with these scripts was that FOREIGN KEY CONSTRAINTS generated duplicate entries on script 1 and 3, to solve this I added a Distinct to the relevent select statements.

    Other than that these scripts worked great, Cheers for publishing them.

  7. I am having problems with the pre-3 script. I am getting these kinds of errors:

    ERROR: Connect.
    Source: ODSOLE Extended Procedure
    Description: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
    [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

    Any idea how to fix this? I am sysadmin on the server.

  8. Ronald, please see my follow-up reply above (September 28, 2010). This will likely solve the connection issue you’ve described.

  9. Pingback: SQL SERVER – Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31 « SQL Server Journey with SQL Authority

  10. Good Afternoon:

    This information is very helpful. If you would be able to fix the link to the downloadable code and documentation, it would be very much appreciated.

    Thanks and have a great day,
    Dave

  11. HI, The two first script that are generated work well (one to drop constraints and index and one to change the collation). But the third one to create indexes and contraint does not work for me. I have this error :

    Error : Load Object, dump buffer
    I am on SqlServer2005

    Can someone help me?

  12. Hi, These scripts have worked well for me, with some changes for computed columns. However, this does not update the collation of the relevant columns in views as defined in INFORMATION_SCHEMA.columns . Has anyone seen problems caused by this?
    Thanks,

  13. Hi,
    I have done all steps in my sql server 2008 r2 system & installed backward compatibility pack also.
    but when i am going to execute the output of delete constraints script
    it gives me following error:
    Description: [SQL-DMO]The name ‘AdventureworksLT2008R2′ was not found in the Databases collection.
    Please help me to resolve this as it would be more helpful for me.

  14. Okay I’m testing this and when I try to recreate my indexes I get the following error

    ERROR: Create DMO Object.
    Source: ODSOLE Extended Procedure
    Description: Invalid class string

    Using SQL Server 2008R2

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

    • Alexandros, my email address is in the article above. Email me directly for a copy of the scripts.

      Please note, these will *not* work with 2008R2 and later.

  16. Brian, this must be a blast from the past… You wrote this in 2009! So… for those of us who searched exactly this issue and somehow found ourselves here in 2014… What would you suggest if we’ve got a large 2008 R2 or 2012 database with lots of tables that have lots of columns, all with the wrong collation? :)

    • Wayne, yes, still a blast that keeps on blasting. I continue to get emails, still to this day. Have you found a solution yet?

      I would suggest a programmed approach. If you have a developer, you can have them reengineer the scripts to use SMO, but follow the basic concept of identifying all offending objects and use SMO to capture their DDL. Base it on the concept of dependency. In other words, tear down tables, procedures, then functions, and then rebuild in the opposite order. While far from perfect, you can get a head start and
      Tweak as necessary. Pay particular attention to the user comments above.

      You could, If there aren’t any R2 and above specific features, migrate to a pre-R2 build and run the original scripts from there.

      Or… You could contract me. You can find my email buried in here if you want to reach out to me directly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s