Solved

SQL server collation error

Posted on 2013-11-21
3
451 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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

919 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

12 Experts available now in Live!

Get 1:1 Help Now