[DB Cleaner on Steroids] SQL Server Collation Conflict with Azure-hosted DB.
db-cleaner-on-steroids
Web icon
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?

Champion

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.

  1. Open the "DBCleanerOnSteroids" module
  2. Find the "GetTables" query inside the "GetTableStorageStats" actions
  3. Add the collation statement "COLLATE SQL_Latin1_General_CP1_CI_AI" in the correct place.


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


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