Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Location
    England
    Posts
    6

    Unanswered: Transfer Database

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

    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.

    Can anyone help?

    Thanks

    Paula.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unless you explicitly created the tables before using the import/export wizard, it would take the collation with the character columns. Check the "new" server to see if it still uses the old collation (you can use Enterprise Manger or sp_help).

    -PatP

  3. #3
    Join Date
    Sep 2004
    Location
    England
    Posts
    6
    Thanks Pat, but I had already checked this. The 'new' server uses the new collation;

    for example table 'CUSTOMER'

    in cp850 DB
    CUS_ShortDesc SQL_Latin1_General_CP850_BIN

    in cp1252 DB
    CUS_ShortDesc Latin1_General_BIN

    Any other ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd check the stored procedures to see if they explicitly forced a collation somewhere. You can scan the script created using Notepad.

    -PatP

  5. #5
    Join Date
    Sep 2004
    Location
    England
    Posts
    6
    I have checked through the errors and found some of the tables have kept the old collation (approx 2000 tables have new coll and 700 have kept the old one). I will look again at the options I used to transfer the tables and see if I can see anything obvious.

  6. #6
    Join Date
    Sep 2004
    Location
    England
    Posts
    6
    We have now 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.

    Any ideas?

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check the QUOTE_IDENTIFIER setting while creating the objects with script.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Sep 2004
    Location
    England
    Posts
    6
    We had this option turned off. I will try it with the option turned on.
    Thanks.

  9. #9
    Join Date
    Sep 2004
    Location
    England
    Posts
    6
    I did not have the option to change the 'Quoted Identifiers' option when scripting the objects. When I transferred the data I set this option on and received the following error;

    [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 1614170346 specified as a default for table ID 234692134, column ID 7 is missing or not of type default.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •