Collation conflict resolution

[MarkButler]

I have a stored procedure which is failing on the following statement/error.

DELETE FROM provider
   WHERE EXISTS (SELECT * FROM t8_work WHERE t8_provid = pvb_provid)
--------------
Server: Msg 446, Level 16, State 9, Line 54
Cannot resolve collation conflict for equal to operation.

Over the weekend the collation for the database was changed using the ALTER DATABASE to change from "LATIN1_GENERAL_BIN" to "SQL_LATIN1_GENERAL_CP1_CI_AS". The table [provider] was defined previous to the ALTER, and the table [t8_work] is defined in the above stored procedure.

I assume that the ALTER caused my collation problem but how is the best way to get things in sync with each other?

Do I need to alter each column of all tables to the new sequence? Do I have to somehow query the collation of the [provider] table and define the [t8_work] table using the same collation sequence? Or is there a better way to get things in sync?

TIA
Mark

 

[NathanGriffiths]

Possible resolutions are;

1. Define your t8_work table in the stored procedure using NCHAR, NVARCHAR and NTEXT Unicode datatypes.

2. Recreate the provider table and reinsert existing data.

3. use CAST and COLLATE in your query e.g.

DELETE FROM provider
   WHERE EXISTS (SELECT * FROM t8_work WHERE CAST(t8_provid AS VARCHAR(xx)) COLLATE LATIN1_GENERAL_BIN = pvb_provid)

Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874