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
Solved

SQL server collation error

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

766 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