ansaurus

Question

Collation conflict in stored procedure while assigning a variable

Answer 1

+3 A:

Normally, this works

SET @MINTIME =
(SELECT CONVERT(varchar,DATEADD(MONTH,-1,GETDATE()),112)) COLLATE DATABASE_DEFAULT;

Or

Only applies to column defs, eg in table variable: ~~DECLARE @MINTIME varchar(??) COLLATE DATABASE_DEFAULT~~

It happens because you have a mismatch somewhere, such as @mintime collation (as DECLARE time) does not match database collation (used by conversion to varchar)

Edit: are you sure it's this line? The error happens in string comparisons normally. On a simple assigmnent the collation of the right hand expressions will be coerced into the left hand collation.

Random thought: line 33, this is where line 1 is "CREATE PROC" usually and exclude the SET ANSI stuff etc added when you right-click, edit in SSMS

gbn 2009-09-22 14:14:03

This results in exactly the same error message unfortunately. Do you have any other suggestions?

iar 2009-09-22 14:20:23

I suspect it's the other way around: @mintime is probably not using the DB default collation

RBarryYoung 2009-09-22 14:28:44

@iar: try SQL_Latin1_General_CP1_CI_AS or Latin1_General_CI_AS

gbn 2009-09-22 14:29:41

In the meantime my colleague discovered that the database server is using a different collation than the database in which we want to create this stored procedure. Both suggestions in this answer still result in error messages. The first in the same error message, the second in "Incorrect syntax near the keyword 'COLLATE'.".

iar 2009-09-22 14:36:18

Although line 33 was identified as the cause of the problem, it was solved by adding some collate statements at the WHERE clause of an insert query in the stored procedure. This WHERE clause however does involve the @MINTIME variable. The WHERE part now reads as follows:WHERE CallLocalTimeString>=@MINTIME AND CallLocalTimeString<=@MAXTIME AND ID COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN (SELECT ID COLLATE SQL_Latin1_General_CP1_CI_AS FROM justsometable))Thanks for your help anyway!

iar 2009-09-23 09:08:39

ansaurus

tags:

views:

answers:

Collation conflict in stored procedure while assigning a variable

related questions