Solved

Need help with Collation Conflict Error.

Posted on 2006-11-10
3
559 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

11 Experts available now in Live!

Get 1:1 Help Now