Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: Replication collation error

    I am trying to set up replication on my SS2K5 server, however after entering the credentials for the Replication user and then hitting ok to set up the distributor I get a bunch of collation errors. I am trying to avoid changing the collation of all my databases on the server, to avoid breaking anything. Is there anyway to get past this error? I tried changing the collation on the distribution database, but I get the following warning.

    Warning: Changing default collation for database 'distribution', which is used in replication. All replication databases should have the same default collation.

    Below are some screenshots that may help explain my situation.
    When I hit ok at this screen.
    http://img24.imageshack.us/img24/294/distributor.jpg
    I get these errors.
    http://img686.imageshack.us/img686/1000/disterror.jpg

    I have edited server information in paint, which is why it may look funny.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I admit I don't know the answer but can help explore some of this with you.

    Is it correct then that the collation of the server is SQL_Latin1_CP1_CI_AS and the collation of the database you want to replicate is Latin1_General_BIN? And that the distribution database is then being created with the default collation of SQL_Latin1_CP1_CI_AS?

    Also for others cross posted here:
    Replication Collation error... - AnandTech Forums

  3. #3
    Join Date
    May 2009
    Posts
    56
    The default collation is

    Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data.

    I get this when I run exec sp_helpsort . I try getting to properties of the database to double check the collation and I get a collation error...

    So I am really confused. This server switched physical machines a few months ago, so I wonder if originally the databases were set up with one collation, and sql server was set up with a different collation when it was set up on the new box.

    Edit:
    I can confirm that the original sql server settings were SQL_Latin1_CP1_CI_AS. Trying to figure out the current sql server settings are....

    Also I am trying to replicate from an Oracle database, if that helps.
    Last edited by coder_t2; 06-04-10 at 15:09.

  4. #4
    Join Date
    May 2009
    Posts
    56
    Ok confirmed. Currently tempdb, model and msdb are Latin1_General_Bin and the rest are SQL_Latin1_CP1_CI_AS.

  5. #5
    Join Date
    May 2009
    Posts
    56
    Ok I think the problem can be fixed if I can change the collation on model, mdsb and tempdb.... not really sure how to change them though since they are system databases. Could someone enlighten me?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Help me out a bit here, you're trying to replicate an Oracle database INTO SQL Server? Microsoft Replication can replicate TO an Oracle Database under certain circumstances, but I don't know of any way to get it to replicate FROM an Oracle database.

    I would very strongly advise you to be absolutely certain that you need/want to change your server collation. Changing a SQL Server Collation is a relatively simple task, but it rarely if ever fixes application problems and frequently means restoring from backups. If you don't have absolute faith in your backups, don't change your collation. Ever.

    The destructions for changing the SQL Server 2005 collation can be found at Setting and Changing the Server Collation if you do decide to do it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    May 2009
    Posts
    56
    Ok thanks for the instructions. I do have have faith in my backups, I have used them to restore the database once before. But I am curious if I misunderstood replication. I want to copy a table from Oracle into my SQL Server database. So I have a copy of this table. Can replication not do this? I though my setting up an Oracle Publisher and SQL Server as the subscriber, that I would be able to copy this table. Please confirm if I am correct in my understanding. Thanks.

  8. #8
    Join Date
    May 2009
    Posts
    56
    Ok I tried rebuilding the master from the command prompt, and I get an message that says please go to control panel to install or remove components. Is there another way to rebuild the master? Also will rebuilding the master change msdb and tempdb databases?

Posting Permissions

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