[DB Cleaner on Steroids] SQL Server Collation Conflict with Azure-hosted DB.
Forge component by Miguel 'Kelter' Antunes
Application Type
Reactive

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.

dbcleanererror.png

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

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.