hi all,
i am facing a strange problem. this is a set of sql statements i am trying to execute. fn_split is the function to split a delimited string. now this was working fine in one sql server, but when i tried running it on another server it says:--
      Server: Msg 446, Level 16, State 9, Line 4
      Cannot resolve collation conflict for like operation.

declare @list varchar(100)
select @list='somename'
select @list=(select parlst from rr_aw_det where aname=@aname)
select pcod
from rr_par_des,fn_split(@list,',')
where rr_par_des.pcod like value+'%'

i am unable to understand , the things which are working fine in one server are unable to run on another.
Any pointers to these will be of great help.


regards
rahul pokharna

__________________
regards
rahul pokharna

Adding info about fn_split. this splits the delimeted string into rows. for ex

delcare s varchar(100)
set s='dfdf,dff,dd,'
select * from fn_split(s,',')
will give the result

idx value
0 dfdf
1 dff
2 dd


regards
rahul pokharna

Looks like you are using the LIKE operator in your fn_split function and it is using a different collation compared to the collation used in your table. Post your fn_split function to help us better understand the problem.

SQL Server Helper
http://www.sql-server-helper.com

See the response I gave to the same question in your other topic:
Moving the database to another Server

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Hi David and sshelper i am giving the code for fn_split and the tables i created . please now let me know y the thing is not working as described above

Tables
rr_aw_det

CREATE TABLE [dbo].[rr_aw_det] (
    [aid] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [aname] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [maxno] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [parlst] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [status] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

rr_par_des

CREATE TABLE [dbo].[rr_par_des] (
    [pcod] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
    [pcat] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
    [pdes] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
    [Control_Type] [int] NULL ,
    [Property] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL
)

and fn_split

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
    @value varchar(8000) ,
    @bcontinue bit,
    @iStrike smallint,
    @iDelimlength tinyint

IF @sDelim = 'Space'
    BEGIN
    SET @sDelim = ' '
    END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
    BEGIN
    WHILE @bcontinue = 1
        BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

        IF CHARINDEX(@sDelim, @sText)>0
            BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END

--Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

            END
        ELSE
            BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            --Exit the WHILE loop.
SET @bcontinue = 0
            END
        END
    END
ELSE
    BEGIN
    WHILE @bcontinue=1
        BEGIN
        --If the delimiter is an empty string, check for remaining text
        --instead of a delimiter. Insert the first character into the
        --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
        IF DATALENGTH(@sText)>1
            BEGIN
            SET @value = SUBSTRING(@sText,1,1)
                BEGIN
                INSERT @retArray (idx, value)
                VALUES (@idx, @value)
                END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

            END
        ELSE
            BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0
            END
    END

END

RETURN
END



Please help me out of this...


regards
rahul pokharna