Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation...


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

Author
Message
tt-615680
tt-615680
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 1201
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!
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
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.

Cool
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)
tt-615680
tt-615680
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 1201
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!
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24259 Visits: 37978
I would look at the two temporary tables #TYear and #LYear and how they are created. Most specifically the countryCode columns.

Cool
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)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8827 Visits: 16567
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" ;-)
tt-615680
tt-615680
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 1201
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!
dineshvishe
dineshvishe
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 342
Dear SSC Veteran,

Thanks A Lot.

Dinesh
daparici
daparici
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Thank you SSC Veteran!
GOODS
GOODS
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 253
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search