InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 15:52:15 GMT20http://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxThank you SSC Veteran!Mon, 29 Apr 2013 09:07:00 GMTdaparicihttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxDear SSC Veteran,Thanks A Lot.DineshWed, 05 Dec 2012 00:13:52 GMTdineshvishehttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxThank 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!Fri, 13 Apr 2012 08:25:44 GMTtt-615680http://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspx[quote][b]tt-615680 (4/12/2012)[/b][hr]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.countryCodeWhat 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![/quote]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[code="sql"]select serverproperty('collation')select databasepropertyex('tempdb', 'collation') [/code]Thu, 12 Apr 2012 10:23:36 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxI would look at the two temporary tables #TYear and #LYear and how they are created. Most specifically the countryCode columns.Thu, 12 Apr 2012 09:27:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxThank 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.countryCodeWhat 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!Thu, 12 Apr 2012 09:22:24 GMTtt-615680http://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspx[quote][b]tt-615680 (4/12/2012)[/b][hr]Dear All,I have a stored procedure and I keep getting the following error message:Msg 468, Level 16, State 9, Procedure "procedurename", Line 129Cannot 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![/quote]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.Thu, 12 Apr 2012 08:58:27 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxCould 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 Thu, 12 Apr 2012 08:58:15 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1282449-391-1.aspxDear All,I have a stored procedure and I keep getting the following error message:Msg 468, Level 16, State 9, Procedure "procedurename", Line 129Cannot 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!Thu, 12 Apr 2012 08:51:04 GMTtt-615680