Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and... Expand / Collapse
Author
Message
Posted Thursday, April 12, 2012 8:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
Dear All,

I have a stored procedure and I keep getting the following error message:

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I looked at the Collation for the Database and it is SQL_Latin1_General_CP1_CI_AS but I don't know what else I need to do to get it working please?


Thank you in advance!
Post #1282449
Posted Thursday, April 12, 2012 8:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 2,856, Visits: 5,124
Could be anything from situation where joining tables have different collation for the columns they are joined on, to using temp tables in the proc and having different default collation in tempdb




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1282456
Posted Thursday, April 12, 2012 8:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
tt-615680 (4/12/2012)
Dear All,

I have a stored procedure and I keep getting the following error message:

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I looked at the Collation for the Database and it is SQL_Latin1_General_CP1_CI_AS but I don't know what else I need to do to get it working please?


Thank you in advance!


Have you looked at line 129 in the procedure to see what is happening in the procedure at that point? We can't tell from here, our crystal balls don't seem to work.

From what you have posted there is a collation difference between two values (or columns). One is using Latin1_General_CI_AS and the other SQL_Latin1_General_CP1_CI_AS.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1282457
Posted Thursday, April 12, 2012 9:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
Thank you for your reply!

This is part of the stored procedure:
........................
...................
CREATE TABLE Data
(
Total_TY decimal(10,2), --this is where is gives the error
Total_LY decimal(10,2),
variance decimal(10,2)
)

INSERT INTO #Data
SELECT
CASE WHEN ISNULL(ISNULL(Total_TY,0)/ISNULL(Total_LY,1), 0) = ISNULL(Total_TY, 0) THEN 100
WHEN ISNULL(ISNULL(Total_LY,0)/ISNULL(Total_TY,1), 0) = ISNULL(Total_LY, 0) THEN -100
ELSE ISNULL((ISNULL(Total_TY,0)/ISNULL(Total_LY,1)), 0) END AS var
FROM #TYear c
LEFT JOIN #LYear p ON c.countryCode = p.countryCode

What I'm confused about is that when I run the same stored procedure on a different Server it runs fine and the setting for the Collation are the same.

Thank you!
Post #1282488
Posted Thursday, April 12, 2012 9:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
I would look at the two temporary tables #TYear and #LYear and how they are created. Most specifically the countryCode columns.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1282491
Posted Thursday, April 12, 2012 10:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 6,365, Visits: 13,695
tt-615680 (4/12/2012)
Thank you for your reply!

This is part of the stored procedure:
........................
...................
CREATE TABLE Data
(
Total_TY decimal(10,2), --this is where is gives the error
Total_LY decimal(10,2),
variance decimal(10,2)
)

INSERT INTO #Data
SELECT
CASE WHEN ISNULL(ISNULL(Total_TY,0)/ISNULL(Total_LY,1), 0) = ISNULL(Total_TY, 0) THEN 100
WHEN ISNULL(ISNULL(Total_LY,0)/ISNULL(Total_TY,1), 0) = ISNULL(Total_LY, 0) THEN -100
ELSE ISNULL((ISNULL(Total_TY,0)/ISNULL(Total_LY,1)), 0) END AS var
FROM #TYear c
LEFT JOIN #LYear p ON c.countryCode = p.countryCode

What I'm confused about is that when I run the same stored procedure on a different Server it runs fine and the setting for the Collation are the same.

Thank you!

What is the collation of your SQL Server instance, Tempdb objects will use the server collation and that's undoubtedly where your issue is. Post the results from the following run against your SQL instance

select serverproperty('collation')
select databasepropertyex('tempdb', 'collation')



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1282562
Posted Friday, April 13, 2012 8:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:09 AM
Points: 249, Visits: 925
Thank you for all your advice!

I used COLLATE DATABASE_DEFAULT and it worked:

Table1.Column1 COLLATE DATABASE_DEFAULT = Table2.Column1 COLLATE DATABASE_DEFAULT


Thank you everyone for you advice!
Post #1283178
Posted Wednesday, December 5, 2012 12:13 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:49 AM
Points: 62, Visits: 326
Dear SSC Veteran,

Thanks A Lot.

Dinesh
Post #1392809
Posted Monday, April 29, 2013 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:57 AM
Points: 1, Visits: 1
Thank you SSC Veteran!
Post #1447585
Posted Thursday, September 19, 2013 5:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:26 AM
Points: 50, Visits: 221
Hi guys,

I'm have same issue:
Msg 468, Level 16, State 9, Procedure RPT_MTX_CDR_CostBreakdown_PerMsisdn, Line 142
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
I have the query below and my error massage is pointing at Line 142 and Line 142 is the SELECT DISTINCT.Please assist.

Select distinct ---------line 142
aa.Msisdn,
bb.box_no
into #MSISDNUnit
from #AllMSISDNCost aa
left join [10.24.4.56\EGHI].wasp_administration_SS.dbo.z_Unit_Lookup bb
on aa.MSISDN COLLATE DATABASE_DEFAULT = bb.MSISDN COLLATE DATABASE_DEFAULT
where bb.msisdn in (Select distinct msisdn from #AllMSISDNCost)
order by bb.box_no--24202
Post #1496313
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse