Post reply

Transfering DB from one collation in SQL 2000 to a different collation in SQL 2000.

  • October 5, 2004 at 9:12 am

    #161638

    Hi,

    We need to transer our databases from codepage 850 to 1252.

    We have succesfully transfered all objects and data EXCEPT stored procedures and triggers using the "Import/Export Data Wizard." We then scripted all the stored procedures and triggers from the cp850 database and installed them into the cp1252 DB. However for quite a few stored procedures and triggers we get the error "Cannot resolve collation conflict for equal to operation." I have tried scripting the sp's with the option 'Only script 7.0 compatible features' on and off but get the same errors each time.

    When I checked the new database it appears that about a quarter of the tables have kept the old collation.

    We then tried a different approach and scripted all objects and installed them in the new DB. We then attempted to transfer the data, however it failed after several hours with the message;

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use empty object or column names. Use a single space if necessary.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 1614173046 specified as a default for table ID 234692134, column 7 is missing or not of type default.

    Object ID 1614173046 is BlankDflt and is a constraint on the table with the ID mentioned above. 

    It was suggested that the QUOTE_IDENTIFIER setting could affect this but changing it resulted in exactly the same error.

    Has anyone changed codepage before and help us with this?

    Thanks for your help

    Paula.

  • October 5, 2004 at 9:44 am

    #525449

    Paula,

    1. After you generate the scripts ( FOR ALL OBJECTS ) from EM, make sure to replace the Collate Statements to the NEW collation. 

    2. Create a new database on the destination server with the NEW collation

    3. Run the Object creation Script on the NEW DB

    4. Transfer the data using DTS or bcp (out-in)

    HTH


    * Noel

  • October 5, 2004 at 9:53 am

    #525451

    Forgot to mention

    step 3.5

    -- Dissable All Triggers and constraints 

    exec sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"

    step 5

    -- Enable All Triggers and Constraints 

    exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"

    exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

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