Solved

Need help with Collation Conflict Error.

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

615 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