error in procedure

• rajnidas.y

  SSC-Addicted

  Points: 431

  More actions

  December 19, 2013 at 2:43 am

  #286480

  hi every one,

  i was not able to solve pls i need help.

  while using store procedure in report getting error as mentioned below !

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

  Thanks

  Rajnidas

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 19, 2013 at 3:09 am

  #1674747

  Rajnidas

  Since you didn't post the code, I can only guess. I should think that your stored procedure is attempting a UNION or JOIN operation between a table in the database and a temp table or variable, and that your database and tempdb have different collations.

  Ways to solve:

  (1) Reinstall SQL Server so that tempdb has the same collation as your database

  (2) Change the collation of your database and of every column in your database to match the collation of tempdb

  (3) Use COLLATE clauses in the column specifications in your stored procedure code

  John

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 19, 2013 at 6:05 am

  #1674775

  i would go with explicitly identifying the collation in the statement;

  here's some code examples you can copy and paste to see the issues:

  an empty table with no data at all in it: this represents the two columns, in your case, one is probably in a differnet table, maybe a different database

  CREATE TABLE #MYSampleData

  ( COLUMNONE CHAR(65) COLLATE Latin1_General_CI_AS,

  COLUMNTWO CHAR(65) COLLATE SQL_Latin1_General_CP1_CI_AS,

  )

  an example query that raises the error: note that the error is in the CharIndex function, according to the message

  SELECT

  COLUMNONE,

  COLUMNTWO,

  CHARINDEX(COLUMNONE,COLUMNTWO),

  CASE

  WHEN COLUMNONE = COLUMNTWO

  THEN 'MATCH'

  ELSE 'NO MATCH'

  END

  FROM #MYSampleData

  Msg 468, Level 16, State 9, Line 3

  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the charindex operation.

  fine, explicitly add COLLATE to teh query and try again:

  SELECT

  COLUMNONE,

  COLUMNTWO,

  CHARINDEX(COLUMNONE COLLATE SQL_Latin1_General_CP1_CI_AS,COLUMNTWO COLLATE SQL_Latin1_General_CP1_CI_AS),

  CASE

  WHEN COLUMNONE = COLUMNTWO

  THEN 'MATCH'

  ELSE 'NO MATCH'

  END

  FROM #MYSampleData

  the error changed a little bit!

  Msg 468, Level 16, State 9, Line 6

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

  finally, we've found all teh conflicts in this specific query!

  SELECT

  COLUMNONE,

  COLUMNTWO,

  CHARINDEX(COLUMNONE COLLATE SQL_Latin1_General_CP1_CI_AS,COLUMNTWO COLLATE SQL_Latin1_General_CP1_CI_AS),

  CASE

  WHEN COLUMNONE COLLATE SQL_Latin1_General_CP1_CI_AS = COLUMNTWO COLLATE SQL_Latin1_General_CP1_CI_AS

  THEN 'MATCH'

  ELSE 'NO MATCH'

  END

  FROM #MYSampleData

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• rajnidas.y

  SSC-Addicted

  Points: 431

  More actions

  December 20, 2013 at 3:21 am

  #1675080

  Thanks for your response sir,

  Rajnidas