Solved

SQL server collation error

Posted on 2013-11-21
3
459 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
[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 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 27

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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