collation error for patindex (sql 2000)

Hi, what do I do with this error please:
Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for patindex operation

Here is the script in question.

use mosaikDB737
exec sp_MSforeachDB
'
use [?]
insert into mosaikDB737.dbo.SearchOutput2 select sk.loginname as searchedTxt, object_name(id) AS ProcName ,Len(SubString(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.loginname))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.loginname)) + ''%'', o.text) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.loginListInput as sk on o.text like ''%'' + ltrim(rtrim(sk.loginname)) + ''%''
ORDER BY searchedTxt,ProcName, Line, position'
select * from mosaikDB737.dbo.SearchOutput2

Thanks a lot

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause. See the COLLATE topic in Books Online for more details on collations, how they are used in string comparisons etc.|||they re different in some databases man. Can I cast loginname to the collation of o.text on the fly dynamically in my script?|||You can't use COLLATE clause dynamically. So you will have to cast both columns to a common collation and then do the comparison. Of course, this means that you will have to pick a collation that will work with your data otherwise you will get incorrect results.|||

that s the pb man. as u can see. using "?" my script goes through all the databases. So if one of the databases has a different collation for the column syscomments.text than the one i will choose then all my script might fail.

|||As I suggested, you change each column that you are comparing to a common collation. This may or may not work depending on the data.|||That was perfect|||

Umachandar Jayachandran - MS wrote:

Check collation of loginname and text columns. Depending on your installation / configuration you may have to modify one or the other to the corresponding collation using the COLLATE clause.

P.S: First I want to let you know that the collation issue has been solved thanks to your help.

I read Collate in BOL but i m still not sure how that works exactly for my script.

for loginname column I have control over it since it s in a database that I create my self. But as you can see text column represents the text for all the SPs of all the databases in the instance.

After checking we found that the text column is 29 times of collation Latin1_General_BIN and 13 times of collation SQL_Latin1_General_CP1_CI_AS.

So my question pls is this:

as you can see that in my script I have the columns sk.LoginName and o.text many times. If I choose to cast both columns to the collation SQL_Latin1_General_CP1_CI_AS. Will I have to put your suggestion COLLATE SQL_Latin1_General_CP1_CI_AS after each occurence of those 2 columns in my script or just in one occurence.

Tell me please if this is correct. I put your suggestion in all the loginname and text occurences in the script (I mean whenever there is loginname or o.text in the script I put COLLATE SQL_Latin1_General_CP1_CI_AS ) as in teh following:

use mosaikDB737
exec sp_MSforeachdb
'
use [?]
insert into mosaikDB737.dbo.SearchOutput1 select sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS as searchedTxt , object_name(id) AS ProcName ,Len(SubString

(o.text,1, PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE

SQL_Latin1_General_CP1_CI_AS))+ ''%'', o.text)))-Len(Replace(SubString(o.text,1, PatIndex
(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'', o.text

COLLATE SQL_Latin1_General_CP1_CI_AS)),char(13),''''))+1 AS Line,
PatIndex(''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%'',

o.text COLLATE SQL_Latin1_General_CP1_CI_AS) AS Position, ''[?]'' as dbName
from syscomments as o inner join mosaikDB737.dbo.LoginListInput as sk on o.text like

''%'' + ltrim(rtrim(sk.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS)) + ''%''
ORDER BY searchedTxt,ProcName, Line, Position'
select * from mosaikDB737.dbo.SearchOutput1 order by dbName, ProcName

I just chose to cast every thing to the collation SQL_Latin1_General_CP1_CI_AS. I hope that s Ok.

Muchas Gracias