Error 446: Cannot resolve collation conflict for equal to operation

Question

Error 446: Cannot resolve collation conflict for equal to operation

boeledi

Ten Centuries

Points: 1151
More actions
December 31, 2008 at 7:44 am

#96871

Dear,
Trying to find out a solution to the fact that I needed to pass to a stored procedure a series of strings to be used in a "IN Clause", browsing the Internet I found out the code of a User-defined function that splits the delimited parameter list and returned a table to be used in the SELECT statement.
However, when I try to save the stored procedure, I obtain the Error 446: Cannot resolve collation conflict for equal to operation message.
Could anybody help me?
Here are the details:
1. User-defined function:
CREATE FUNCTION dbo.fct_ListToTable (@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
Valuevarchar(8000) not null
)
AS
BEGIN
--Declare helper to identify the position of the delim
DECLARE @DelimPosition INT
--Prime the loop, with an initial check for the delim
SET @DelimPosition = CHARINDEX(@delim, @list)
--Loop through, until we no longer find the delimiter
WHILE @DelimPosition > 0
BEGIN
--Add the item to the table
INSERT INTO @listTable(Value)
VALUES(RTRIM(LEFT(@list, @DelimPosition - 1)))
--Remove the entry from the List
SET @list = right(@list, len(@list) - @DelimPosition)
--Perform position comparison
SET @DelimPosition = CHARINDEX(@delim, @list)
END
--If we still have an entry, add it to the list
IF len(@list) > 0
insert into @listTable(Value)
values (RTRIM(@list))
RETURN
END
2. Stored procedure:
CREATE PROCEDURE dbo.sp_test
(
@in_listnvarchar(512)= ''
)
AS
BEGIN
SELECT
*
FROM
tbl_category
WHERE
category_name in (
SELECT
Value
FROM
dbo.fct_ListToTable(@in_list, ',')
)
END
GO
3. Definition of the columns
TABLE tbl_category
....
category_name nvarchar(512) not null
In advance, many thanks for any help.
Didier

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

It looks like your collation settings for the table are different than that for the database, check that these are the same.

boeledi Ten Centuries Points: 1151 More actions · Answer 2

Thanks for your answer but could you be a little bit more explicit? What do I have to check?

thanks in advance,

steveb. SSC-Forever Points: 46733 More actions · Answer 3

Check the properties of the database (right click), have a look at the collation setting.

Check the properties of the table --> Extended properties, have a look at the collation setting.

Check that these are the same..

boeledi Ten Centuries Points: 1151 More actions · Answer 4

You saved my day...

Here is the modification I needed to apply to the User-Defined function:

CREATE FUNCTION dbo.fct_ListToTable (@list as varchar(8000), @delim as varchar(10))

RETURNS @listTable table (

Valuevarchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

AS

BEGIN

--Declare helper to identify the position of the delim

DECLARE @DelimPosition INT

--Prime the loop, with an initial check for the delim

SET @DelimPosition = CHARINDEX(@delim, @list)

--Loop through, until we no longer find the delimiter

WHILE @DelimPosition > 0

BEGIN

--Add the item to the table

INSERT INTO @listTable(Value)

VALUES(RTRIM(LEFT(@list, @DelimPosition - 1)))

--Remove the entry from the List

SET @list = right(@list, len(@list) - @DelimPosition)

--Perform position comparison

SET @DelimPosition = CHARINDEX(@delim, @list)

END

--If we still have an entry, add it to the list

IF len(@list) > 0

insert into @listTable(Value)

values (RTRIM(@list))

RETURN

END

thanks to the "COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL" statement, it now works perfectly !

Once again, thanks a lot.

Didier

steveb. SSC-Forever Points: 46733 More actions · Answer 5

No worries, glad that it worked out for you.

Collation affects the way that string values are sorted and compared and can sometimes can cause funny issues.