[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL server collation error

Posted on 2013-11-21
3
Medium Priority
?
474 Views
Last Modified: 2013-11-21
I am trying to execute sql script in SQL Server 2012 with linked server setup but it is showing this error.

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Please find the attached script.

I tried to collate tables with different collations but it did not worked.

Pls suggest me how to resolve this issue.
SQLQuery.sql
0
Comment
Question by:sqldba2013
3 Comments
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 600 total points
ID: 39666388
just a note it's easier if you embed this in code rather than attaching a file.

You have to do this on a column by column basis:

SELECT distinct PRA.inq_num collate SQL_Latin1_General_CP1_CI_AS,[PO Number] collate SQL_Latin1_General_CP1_CI_AS

FROM REA.dbo.ps_requests_asr PRA with(nolock) 
left join [Link_PROD].BCA.dbo.CPOA with(nolock)  ON Inquiries 
collate SQL_Latin1_General_CP1_CI_AS = '[' + PRA.inq_num + ']' 
WHERE  
	--PRA.inq_num='10521271'  and
	PRA.inq_num is not null
	AND cpoa.[sales org]='X'
	collate SQL_Latin1_General_CP1_CI_AS

Open in new window


or in your create table do the collation there:

CREATE TABLE #temp
(
      [Region] VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS,
[Market] VARCHAR(150) collate SQL_Latin1_General_CP1_CI_AS
--... etc
)
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 400 total points
ID: 39667136
The problem is this: The 2 servers seem to have different collations.  When you create a temp table(#) by default will use the server's collation where it is created. However your table on the linked server will have a different collation which causes conflict. In this case you will have to explicitly specify the collation for every varchar, nvarchar, text and ntext column or when you compare the columns from temp table with other table you will have to COLLATE it in one of the sides to the collation from the other side, which means to specify COLATE collation after the column name to match the collation for the column of the other side of compare operator. I.E:

linked..col_varchar COLLATE local_server_collation = #tmp.col_varchar

or

linked..col_varchar = #tmp.col_varchar COLLATE linked_server_collation
0
 

Author Closing Comment

by:sqldba2013
ID: 39667905
--
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

872 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