I have a customer with an self-hosted infra and SQL Server database. We're getting the following error when clicking the 'Table Space' button:
Error executing query. Error in advanced query GetTables in GetTableStorageStats in DBCleanerOnSteroids (select 1 AS IsHeader, 'ALL ESPACES', 'ALL ENTITIES', 'ALL TABLES', sum (RowCounts), sum (TotalSpaceMB), sum (UsedSpaceMB), sum (UnusedSpaceMB) from ( SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8/ 1024.0 AS TotalSpaceMB, SUM(a.used_pages) * 8/ 1024.0 AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/ 1024.0 AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND (@IncludeEmptyTables = 1 OR ( @IncludeEmptyTables = 0 AND p.rows > 0 )) GROUP BY t.Name, p.Rows ) totals union all SELECT 0 AS IsHeader, e.name eSpace, ...): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation.
I understand it's a collation conflict, just not sure how to fix it.
@Jay Parnau did you have any luck solving this, if so how?
Hello Jay
Might be "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" are different collation on the two databases or tables.
Can you try to do this with Collate?
Please look into the sample Solution, I found -
Hi @Jay Parnau ,
The same thing happened to me.You need to update the query inside the module.
As you can see on the image above, this LEFT JOIN is trying to relate two different databases: "sys" and the "OS database". And it seems they have different collations. It happens.
Remember, this solution will not help in all cases, and it is not advisable to use this everywhere you find this error.
The COLLATE statement is ANSI and also be supported by Oracle and SQL Server. But the collation name should match with the error message to work properly.
Please, check the SQL Server documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/collation-precedence-transact-sql?view=sql-server-ver15