Changing Rogue Database Collations

  • Mike Pearson

    Default port

    Points: 1481

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mpearson/changingroguedatabasecollations.asp

  • AFPeterson

    SSCommitted

    Points: 1580

    Looks like a good, common sense approach. Hope I never have to use it!


    What's the business problem you're trying to solve?

  • Mike Pearson

    Default port

    Points: 1481

    Cheers for that - *sigh* I have a client that FOUR different collations to look after - still haven't convinced them to take the plunge yet....

  • fred1234

    SSC Enthusiast

    Points: 148

    Mike, thanks for this article it saved me alot of work recently when converting a database from SQL 7 to 2000 and the two server builds had different collations.

    There were a couple of issues that I ran into that I wanted to let you know about.

    1. When scripting the database across there were some stored procedures that were encrypted and they didn't get scripted out but the grant statements for it did. Fortunatly I had the originals and could script them in.

    2. I also ran into a problem with identity fields being reset. The case in point was that we have a transactional table where records are constantly being written to by one app then read, processed and deleted by another app and an identity field is used as a key on this table. We also have a history table which stores details about transactions being processed and it uses the identity field from the transactional table as its primary key. When doing the conversion the transactional table was empty so the identity field was reset to 1 and when trying to add new transactions to the history table we got primary key violations.

    Edited by - fred1234 on 09/10/2003 11:04:21 AM

    Edited by - fred1234 on 09/10/2003 11:09:04 AM

  • Mike Pearson

    Default port

    Points: 1481

    Hi Fred1234 - yes recreating a table with and indentity column would reseed that table to 1 - you'd have to create that table and reseed the new table to the correct identity value. But I expect that's what you did....

  • Botond

    Right there with Babe

    Points: 778

    Hi Mike!

    You saved me from a lot of trouble!

    For a smaller database it took me a half day to get done this job done "manual".

    When I got to a bigger one, I sad no way I am going to do the same thing on this "manual", so I started looking, that's how I ran into your article.

    Thanx again!

    Your information saved me a lot of time and energy - I did not had to invent the wheel again 🙂

    It worked 99% smooth. I had a little problem with a few (3) tables when I was migrating the data (1 - had computed columns, 2 - had special check constraints), but I've corrected those by hand. :)) Anyway: from I donn't know how many 1000 of objects, I say this is a pretty good rate. :))

    And again:

    Thank you very much!

    Thank you very much for sharing your knowledge!

    Best regards,

    Boti

    Edited by - botond.baika on 12/15/2003 06:17:26 AM


    🙂

  • dwebb

    SSC Veteran

    Points: 277

    what if you just used the DTS copy sqlserver objects task and set the ScriptOptionEx integer value to 8388608

    which translates to;

    Do not script the collation clause if source is an instance of SQL Server version 7.0 or later. The default is to generate collation.not script the collation clause if source is an instance of SQL Server version 7.0 or later. The default is to generate collation.

     

     

    ?

  • Niall Merrigan

    Valued Member

    Points: 67

    This article just saved me hours of work.. many thanks, works like a treat

  • Mauro Ciaccio

    SSCrazy

    Points: 2507

    Hi,

    Let me add my voice to the chorus of compliments. GREAT WORK!

    The only problem I encountered was with timestamp columns, for which I kept getting the following error:

    Server: Msg 273, Level 16, State 1, Line 3

    Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.

    So I had to modify all my statements to remove the insert NULL values into the timestamp columns. I would immagine the best thing though would be to not include those columns in the INSERT scripts at all. Do you agree?

    Mauro Ciaccio

  • Mauro Ciaccio

    SSCrazy

    Points: 2507

    ....thought it was too simple.....

    By writing NULL to the timestamp columns, the checksum calculation done by your Script2 then fails.

    Mauro Ciaccio

  • Aardvark-240975

    Grasshopper

    Points: 23

    Hi there.

    I am currently looking at ways of migrating databases to a SAN / SQL Server Cluster environment and you guessed it, the collation on the new Virtual Server is different from the original.

    I tried this script out and it works very well with one exception.  On scripting the 'xxxx_Old' database the scripted views were in the wrong order of creation and had to be re-arranged before running and creating the 'xxxx' database.

    I have also had a look at DTS copy sqlserver objects task which also seems to do the job.

    Any comments on which method is better to use?

    Many Thanks for sharing your ideas.

    Aardvark

  • Jason-368451

    SSC Enthusiast

    Points: 127

    Thank you!!

    This process worked great except my master database collation was different from my new re-created database so I had to modify the script.

    I was getting error

    "Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

    so i modify these two parts of the script

    -- Add the tables that have no Foreign-Key relationships...

    INSERT INTO #Hierarchy

    SELECT -1, [name] COLLATE SQL_Latin1_General_CP1_CI_AS, ' - '

    FROM dbo.sysobjects

    WHERE [name]COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)

    AND [Name]COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT DISTINCT Child FROM #Hierarchy)

    AND xtype = 'U'

    -- =====================================================

    -- Identify tables that will require identity insert...

    -- =====================================================

    UPDATE #TableInfo

    SET IdentityField = 'Y'

    FROM dbo.sysobjects

    INNER JOIN dbo.syscolumns

    ON dbo.sysobjects.id = dbo.syscolumns.id

    WHERE dbo.syscolumns.status = 0x80

    AND dbo.sysobjects.name COLLATE SQL_Latin1_General_CP1_CI_AS = #TableInfo.TableName

    Just thought I would make the post in case anyone else has the same problem

  • SanjayAttray

    SSChampion

    Points: 13157

    HI,

    When I execute script1 from the article I get this error:

    Server: Msg 536, Level 16, State 3, Line 210

    Invalid length parameter passed to the substring function.

    My database default collation is set to Latin1_General_CS_AI

    Any idea or resolution would be appreciated.

    Thanks.

    SQL DBA.

  • SanjayAttray

    SSChampion

    Points: 13157

    Anyone here?

    Just to add. Error is in this line.

    SET @strColumnList = left(@strColumnList,len(@strColumnList)-1).

    I tried differently but could not get the result I wanted. like.

    SET @strColumnList = LEFT(@strColumnList, CHARINDEX(' ', @strColumnList) - 1)

    SET @strColumnList = LEFT(@strColumnList, NULLIF(CHARINDEX(' ', @strColumnList) - 1, -1))

    SQL DBA.

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

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