Solved

SQL server collation error

Posted on 2013-11-21
3
449 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 39

Accepted Solution

by:
Kyle Abrahams earned 150 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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

758 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

23 Experts available now in Live!

Get 1:1 Help Now