Solved

Need help with Collation Conflict Error.

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 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