collation conflict in union conflict

  • Hello All - I am trying to run the following statement but it keep erroring due to a collation conflict - Without changing the database collation can someone help me run the following;

    DECLARE @DatabaseName VARCHAR(50)

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = ''

    DECLARE DatabaseCursor CURSOR FOR

    SELECT name

    FROM sys.databases

    WHERE name not in

    ('master','model','msdb','tempdb','reportserver','ReportServerTempDB','distribution')

    OPEN DatabaseCursor

    FETCH NEXT FROM DatabaseCursor into @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF (LEN(@SQL) > 0)

    BEGIN

    SET @SQL = @SQL + ' UNION '

    END

    SET @SQL = @SQL + '

    SELECT''' + @DatabaseName + ''' as DatabaseName,

    o.name as TableName,

    i.name as IndexName,

    COALESCE(user_seeks + user_scans + user_lookups, 0) as Reads,

    COALESCE(user_updates, 0) AS Writes,

    p.rows AS Rows

    FROM ' + @DatabaseName + '.sys.objects o, ' + @DatabaseName + '.sys.partitions p, ' + @DatabaseName + '.sys.indexes i

    LEFT OUTER JOIN ' + @DatabaseName + '.sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id

    WHERE i.object_id = o.object_id

    AND p.object_id = o.object_id

    AND p.index_id = i.index_id

    AND o.type = ''U''

    AND o.name not like ''[_]%'''

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

    END

    CLOSE DatabaseCursor

    DEALLOCATE DatabaseCursor

    SET @SQL = @SQL + ' ORDER BY DatabaseName, TableName, IndexName'

    EXEC(@SQL)

    Error message I am getting is; Msg 468, Level 16, State 9, Line 2

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.'

    I have tried COLLATE DATABASE_DEFAULT but without any luck.... Thanks!

    --------------------------------------------

    Laughing in the face of contention...

  • Instead of building one statement to execute, I would execute each statement into a temp table.

    CREATE TABLE #Indexes

    (DatabaseName SYSNAME,

    TableName SYSNAME,

    IndexName SYSNAME NULL,

    Reads INT,

    Writes INT,

    Rows INT);

    DECLARE @DatabaseName VARCHAR(50)

    DECLARE @SQL NVARCHAR(MAX)

    DECLARE DatabaseCursor CURSOR FOR

    SELECT name

    FROM sys.databases

    WHERE name not in

    ('master','model','msdb','tempdb','reportserver','ReportServerTempDB','distribution')

    OPEN DatabaseCursor

    FETCH NEXT FROM DatabaseCursor into @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @SQL = '

    SELECT''' + @DatabaseName + ''' as DatabaseName,

    o.name as TableName,

    i.name as IndexName,

    COALESCE(user_seeks + user_scans + user_lookups, 0) as Reads,

    COALESCE(user_updates, 0) AS Writes,

    p.rows AS Rows

    FROM ' + QUOTENAME(@DatabaseName) + '.sys.objects o, ' + QUOTENAME(@DatabaseName) + '.sys.partitions p, ' + QUOTENAME(@DatabaseName) + '.sys.indexes i

    LEFT OUTER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id

    WHERE i.object_id = o.object_id

    AND p.object_id = o.object_id

    AND p.index_id = i.index_id

    AND o.type = ''U''

    AND o.name not like ''[_]%'''

    INSERT INTO #Indexes

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

    END

    CLOSE DatabaseCursor

    DEALLOCATE DatabaseCursor

    SELECT

    *

    FROM

    #Indexes

    ORDER BY

    DatabaseName,

    TableName,

    IndexName;

    DROP TABLE #Indexes;

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Thanks for your response Sean. Your example code works perfectly.

    I was trying to get around the fact of using a temp table but if that's the only way then it should be fine. Thanks again, Russell.

    --------------------------------------------

    Laughing in the face of contention...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply