collation error message for patindex

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

collation error message for patindex

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-09 : 17:58:16

Hi, what do I do with this error please:
Error:
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 18:09:23

Try to include the COLLATE keyword after the columns that are throwing this error.

Peter Larsson
Helsingborg, Sweden

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-09 : 20:36:09

what d be the syntax pls?
as we can see, dear, this is the part that causes the error:

PatIndex(''%'' + ltrim(rtrim(sk.loginname))+ ''%'', o.text))
sk is just a table that i create myself which means that i can alter it and its columns including sk.loginname but all the other databases and especially syscomments which gives as the as o table in the script. I have no control to change its collation.
Sorry, i don t understand the colation topic well.

Thanks

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-09 : 22:11:27

i did that, it worked well. hoe do i specify that i want sk.loginname collated to the collation of o.text in all the script. Or each part of the script collated to the collation of o.text (dynamically on the fly i mean when we don t know the collation of o.text in advance)
Is there a simple way
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources