Unanswered: Collation problem, possibly with a Replication twist
I''m having trouble getting COLLATE to work in the SQL below, and from the error I get I think it may have something to do with Replication.
In this example my standard collation is Latin1_General_CI_AS, and 'Collateral' is a replicated database that uses French_CI_AI collation.
First, here's the query without any Collate statements in it:
if exists (select 1 from [Collateral]..sysobjects where name = 'sysmergesubscriptions')
select isnull(p.publisher, pub.srvname),
j.name, ri.merge_jobid, null, 1, m.db_name, m.subscriber_server
from [Collateral].dbo.sysmergesubscriptions m
left outer join master.dbo.sysservers pub
on m.srvid = pub.srvid
left outer join [Collateral]..sysmergepublications p
on m.pubid = p.pubid
left outer join [Collateral]..MSmerge_replinfo ri
on m.subid = ri.repid
left outer join msdb..sysjobs j
on ri.merge_jobid = j.job_id
where (m.subid <> m.pubid or m.db_name <> p.publisher_db)
and m.subscriber_type <> 1
and (isnull(p.publisher, pub.srvname) <> m.subscriber_server or isnull(p.publisher_db,m.db_name) <> 'Collateral')
and not exists (select 1 from dbo.Spot_Repl_Agents a where a.Publisher = isnull(p.publisher, pub.srvname)
and a.PublisherDBName = isnull(p.publisher_db,m.db_name)
and a.PublicationName = isnull(p.name, publication)
and a.Subscriber = m.subscriber_server
and a.SubscriberDBName = m.db_name)
Now, using my limited knowledge of Collate, I inserted Collate statements everywhere m.<name> was being used (from the where statement on).
ie: where (m.subid <> m.pubid or m.db_name COLLATE Latin1_General_CI_AS <> p.publisher_db)
If I also insert a collate before the 'or' in that line I get a "Expression type uniqueidentifier is invalid for COLLATE clause" error, which is why I think there might be a Replication link to the problem.
Additionally - hey, why stop while you're on a roll! - do I have to hard code in the standard collation (Latin_etc), or is there something more generic I can do instead?