CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nida
Nida

Reputation: 1702

Issue in join operation

I have created stored procedure as below lines of code

    ALTER PROCEDURE [dbo].[prJurisdictionFetchForAgentLicence]
@AgentId int
As
Declare @individual varchar(20) = null;

Create table #Split 
(
Jurisdiction nvarchar(max)
)
Insert into #Split select Jurisdiction from tbLicence where AgentId =@AgentId;

Select * from #Split;

DECLARE @products VARCHAR(MAX)='';

SELECT @products += Isnull(Jurisdiction, '') + ',' FROM   #Split;

SELECT right(@products,len(@products)-1); 

Create table #TempJurisdiction
(
JurisdictionX nvarchar(max)
);

Insert into #TempJurisdiction 
SELECT 
 Split.a.value('.', 'VARCHAR(MAX)') AS TempJurisdictiom
FROM  
(
SELECT CAST ('<M>' + REPLACE(@products, ',', '</M><M>') + '</M>' AS XML) AS CVS 
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a);

Select * from #TempJurisdiction;

SELECT L.Jurisdiction,
L.JurisdictionX
FROM   tbJurisdiction L
JOIN #TempJurisdiction P
ON P.JurisdictionX = L.JurisdictionX

It's throwing error message while executing this stored procedure

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and Latin1_General_CI_AI" in the equal to operation.

Upvotes: 1

Views: 69

Answers (1)

Kritner
Kritner

Reputation: 13765

Your collations should be similar, just change the collation on the table that's "less normal" than your other db tables

select *
from table1
join table2 on (table1.field collate SQL_Latin1_General_CP1_CI_AS = table2.field)

as an example. My guess would be your tempDb has a different collation than the DB your SP resides and that's why you're getting the issue - just a guess though.

Here's a blog post related to your issue: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

Basically one of your collations states "accent insensitive" (AI) while the other states "accent sensitive" (AS) - trying to compare equality between different collations such as this, you could probably see why the error would be thrown without making the collations similar.

Upvotes: 1

Related Questions