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;
-
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