Solved

Need help with Collation Conflict Error.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing Parameter to Stored Procedure 4 24
VB.NET 2008 - SQL Timeout 9 24
Alternative of IN Clause in SQL Server 3 21
Sql Query 6 66
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 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