Solved

Need help with Collation Conflict Error.

Posted on 2006-11-10
3
568 Views
Last Modified: 2012-06-21
I created the following procedure and I'm getting the following error:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[ssp_check_cstcnt]
 AS
 select distinct costcenter from general_ledger
where costcenter not in ( select cstcnt from costcenters)

Msg 468, Level 16, State 9, Procedure ssp_check_cstcnt, Line 5
Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_BIN2" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


I thought it might be due to the fact that the default for this SQL database was set to SQL_Latin1_General_CP437_BIN2 at installation time and the procedure was created on a SQL 2000 server and imported to the newer version.  I've modified the options for the database hosting this procedure to SQL_Latin1_General_CP1_CI_AS but I'm still getting the same error.  Can anyone tell me what I need to do to resolve this error without modifying the code for each of the imported procedures?
0
Comment
Question by:abbdan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17918578
You would have to change the collation on the column costcenter to SQL_Latin1_General_CP437_BIN2

This is because the tempdb (part of the installation of the server) will be running with SQL_Latin1_General_CP437_BIN2 collation - any operation that uses tempdb (explicitly or implicitly, and I assume that this will actually result in a hash join in tempdb) will give you this error.

or perhaps I have misread this, and you need to change the collation to SQL_Latin1_General_CP1_CI_AS ;)
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17920306
ok you need to change the collation of one of the two columns
and make sure that

CostCenter in General_ledger and cstcnt in CostCenters have same collation
0
 
LVL 4

Author Comment

by:abbdan
ID: 17924386
Strangly enough the cstcnt field maintained the SQL_Latin1_General_CP437_BIN2 even though I changed the whole database option to SQL_Latin1_General_CP1_CI_AS.  Not sure I understood how why but when I changed the database option property back to SQL_Latin1_General_CP437_BIN2 the procedure started functioning properly.

Thanks for the help Nightman.  Thanks for the reply imran fast but Nightman was a little bit faster :)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

734 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