Cannot Resolve collation conflict [SQLSTATE 42000]

Posted on 2007-08-09
Last Modified: 2008-02-07
I'm getting the following error on a procedure:

Msg 446, Sev 16: Cannot resolve collation conflict for equal to operation. [SQLSTATE 42000]
Job 'DPSU Remove Employee Subgroup' : Step 1, 'REMOVE EMPLOYEE SUBGROUP' : Began Executing 2007-08-09 08:00:00

From what I've found, this can occur when the collation of the database doesn't match the master or tempdb collation. However, they all match.

One pertinent issue, is that the Instance this DB originated from does have a different collation to the one it resides on now.

Original DB=
Latin1-General,Case-Insensitive,Accent-Sensitive,Kanatype-Insensitive,Width-Insensitive Unicode Data,Sort Order 52 for non unicode data

New DB=

These procedures have worked for about 3 years without a problem. They were written by the Application Vendor, so even if putting in a Collate statement resolves the problem, the procedures would then become unsupported.

The only thing I can think of is to get a completely new server, install MSSQL with the exact same Collation, and restore the db onto there and test. Only thing is that this DB has gone into production, so they're not going to be keen.

Are there any alternatives, or would I have to start afresh with a new install?

Question by:Beeb_Hogweed
    LVL 14

    Accepted Solution

    Rebuild the master database with the original collation.

    Author Comment

    In this case, that means installing SQL Server fresh, as there are other DB's on this instance. It was a case of them being cheapskates not getting a separate server for this DB. I'll install afresh with the same collation and try again.

    LVL 14

    Expert Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now