Cannot resolve collation conflict for equal to operation.

I'm trying to audit our sql server and getting a collation error. "Cannot
resolve collation conflict for equal to operation." I know 3 of the
databases on this server have a different collation, but I need to find a wa
y
to get around this since I can't change the collation because they are vendo
r
databases. Does anyone have any ideas? I'm at a loss and this is due to our
auditors within the next few days. Thanks in advance for your help! This is
the code I'm using to audit:
DECLARE @.spname VARCHAR (128)
DECLARE @.dbname VARCHAR (128)
DECLARE @.logiNAME VARCHAR (2000)
DECLARE @.cmd VARCHAR (5000)
-- Declare cursor
DECLARE sp_csr INSENSITIVE CURSOR FOR
select name as dbname from sysdatabases
order by name
-- Open the cursor
OPEN sp_csr
-- Loop through all the databases in the database
FETCH NEXT
FROM sp_csr
INTO @.dbname
print @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
''OBJECT NAME'' = c.name, 30,
''ACTION'' = CASE a.action
WHEN 26 THEN ''REFERENCES''
WHEN 193 THEN ''SELECT''
WHEN 195 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''UPDATE''
WHEN 198 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 204 THEN ''GRANT_W_GRANT''
WHEN 205 THEN ''GRANT''
WHEN 206 THEN ''REVOKE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''DUMP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''DUMP TRANSACTION''
WHEN 236 THEN ''CREATE RULE''
END,
"TYPE" = CASE c.type
WHEN ''C'' THEN ''CHECK constraint''
WHEN ''D'' THEN ''Default or DEFAULT constraint''
WHEN ''F'' THEN ''FOREIGN KEY constraint''
WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
WHEN ''L'' THEN ''Log''
WHEN ''P'' THEN ''Stored procedure''
WHEN ''R'' THEN ''Rule''
WHEN ''RF'' THEN ''Stored procedure for replication''
WHEN ''S'' THEN ''System table''
WHEN ''TR'' THEN ''Trigger''
WHEN ''U'' THEN ''User table''
WHEN ''V'' THEN '' View''
WHEN ''X'' THEN ''Extended stored procedure''
WHEN ''FN'' THEN ''User Defined Function''
END,
ServerName=(select srvname from master..sysservers where srvid = ''0''),
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
, Environment = Case when (select srvname from master..sysservers where
srvid = ''0'') like ''Dev%''
THEN ''DEV''
WHEN (select srvname from master..sysservers where srvid = ''0'') like
''Prod%''
THEN ''PROD''
WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
''Test%''
THEN ''TEST''
ELSE ''Unknown''END
FROM sysprotects a, sysusers b, sysobjects c
WHERE a.uid = b.uid
AND c.id = a.id
--AND b.name = @.group
ORDER BY b.name, c.name, a.action'
--PRINT @.cmd
EXEC (@.cmd)
FETCH NEXT
FROM sp_csr
INTO @.dbname
END
-- Close and deallocate the cursor
CLOSE sp_csr
DEALLOCATE sp_csrat a glance, i'm not seeing anything jump out - first step would be
capturing the @.cmd that errored, run it separately and narrow down which
exact part is the problem.
It is possible to coerce a query to use a specified collation - after
the criteria. e.g.
...from table1 join table2 on table1.column = table2.column collate
<collation>...
...from table1 where column='some value' collate <collation>
Anita wrote:

>I'm trying to audit our sql server and getting a collation error. "Cannot
>resolve collation conflict for equal to operation." I know 3 of the
>databases on this server have a different collation, but I need to find a w
ay
>to get around this since I can't change the collation because they are vend
or
>databases. Does anyone have any ideas? I'm at a loss and this is due to our
>auditors within the next few days. Thanks in advance for your help! This is
>the code I'm using to audit:
>DECLARE @.spname VARCHAR (128)
>DECLARE @.dbname VARCHAR (128)
>DECLARE @.logiNAME VARCHAR (2000)
>DECLARE @.cmd VARCHAR (5000)
>
>-- Declare cursor
>DECLARE sp_csr INSENSITIVE CURSOR FOR
>select name as dbname from sysdatabases
>order by name
>-- Open the cursor
>OPEN sp_csr
>-- Loop through all the databases in the database
>FETCH NEXT
> FROM sp_csr
> INTO @.dbname
> print @.dbname
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> SELECT @.cmd = 'Use ' + @.dbname + ' '+ 'SELECT ''ROLE NAME'' = b.name, 30,
>''OBJECT NAME'' = c.name, 30,
>''ACTION'' = CASE a.action
>WHEN 26 THEN ''REFERENCES''
>WHEN 193 THEN ''SELECT''
>WHEN 195 THEN ''INSERT''
>WHEN 196 THEN ''DELETE''
>WHEN 197 THEN ''UPDATE''
>WHEN 198 THEN ''CREATE TABLE''
>WHEN 203 THEN ''CREATE DATABASE''
>WHEN 204 THEN ''GRANT_W_GRANT''
>WHEN 205 THEN ''GRANT''
>WHEN 206 THEN ''REVOKE''
>WHEN 207 THEN ''CREATE VIEW''
>WHEN 222 THEN ''CREATE PROCEDURE''
>WHEN 224 THEN ''EXECUTE''
>WHEN 228 THEN ''DUMP DATABASE''
>WHEN 233 THEN ''CREATE DEFAULT''
>WHEN 235 THEN ''DUMP TRANSACTION''
>WHEN 236 THEN ''CREATE RULE''
>END,
>"TYPE" = CASE c.type
>WHEN ''C'' THEN ''CHECK constraint''
>WHEN ''D'' THEN ''Default or DEFAULT constraint''
>WHEN ''F'' THEN ''FOREIGN KEY constraint''
>WHEN ''K'' THEN ''PRIMARY KEY or UNIQUE constraint''
>WHEN ''L'' THEN ''Log''
>WHEN ''P'' THEN ''Stored procedure''
>WHEN ''R'' THEN ''Rule''
>WHEN ''RF'' THEN ''Stored procedure for replication''
>WHEN ''S'' THEN ''System table''
>WHEN ''TR'' THEN ''Trigger''
>WHEN ''U'' THEN ''User table''
>WHEN ''V'' THEN '' View''
>WHEN ''X'' THEN ''Extended stored procedure''
>WHEN ''FN'' THEN ''User Defined Function''
>END,
>ServerName=(select srvname from master..sysservers where srvid = ''0''),
>DatabaseName=(SELECT master..sysdatabases.name
>FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
>master..sysdatabases.filename)
>, Environment = Case when (select srvname from master..sysservers where
>srvid = ''0'') like ''Dev%''
> THEN ''DEV''
> WHEN (select srvname from master..sysservers where srvid = ''0'') like
>''Prod%''
> THEN ''PROD''
> WHEN (select srvname from master..sysservers where srvid = ''0'')LIKE
>''Test%''
> THEN ''TEST''
> ELSE ''Unknown''END
>FROM sysprotects a, sysusers b, sysobjects c
>WHERE a.uid = b.uid
>AND c.id = a.id
>--AND b.name = @.group
>ORDER BY b.name, c.name, a.action'
>
> --PRINT @.cmd
>EXEC (@.cmd)
> FETCH NEXT
> FROM sp_csr
> INTO @.dbname
>END
>
>-- Close and deallocate the cursor
>CLOSE sp_csr
>DEALLOCATE sp_csr
>|||Change
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename =
master..sysdatabases.filename)
to
DatabaseName=(SELECT master..sysdatabases.name
FROM dbo.sysfiles INNER JOIN
master..sysdatabases ON dbo.sysfiles.filename COLLATE
Latin1_General_CI_AS =
master..sysdatabases.filename COLLATE Latin1_General_CI_AS)|||That worked as far as getting those databases that have a different
collation's info, but I'm still getting errors. Now I'm getting these errors
:
Line 41: Incorrect syntax near 'COLLATE'.
Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'like'.
Thanks!
"markc600@.hotmail.com" wrote:

> Change
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename =
> master..sysdatabases.filename)
> to
> DatabaseName=(SELECT master..sysdatabases.name
> FROM dbo.sysfiles INNER JOIN
> master..sysdatabases ON dbo.sysfiles.filename COLLATE
> Latin1_General_CI_AS =
> master..sysdatabases.filename COLLATE Latin1_General_CI_AS)
>