Not had this before, and took me a while to figure out, so posting in case it saves someone else …
From Database1 run dynamic SQL on Database2, both databases have different default collation.
I am familiar with comparison of columns BETWEEN databases as needing consideration for Collation - and, indeed, between Tables & Columns WITHIN a database if they happen to have different collations, although that is very rare for us.
Also, comparing a char/varchar column to an @Variable has never needed collation …
… but today I added a COALESCE to the @Variable and that blew up - presumably because COALESCE defaults to the current-database collation, rather than the collation of the database where the dynamic SQL was targetted:
USE MyDatabase1 -- Database Collation = SQL_Latin1_General_CP1_CI_AS
DECLARE @Schema sysname = 'dbo'
DECLARE @strSQL nvarchar(MAX)
SELECT @strSQL = '
USE MyDatabase2; -- Database Collation = Latin1_General_CI_AS
SELECT TOP 1
FROM sys.databases AS DB
LEFT OUTER JOIN sys.schemas AS S
ON S.name = COALESCE(@Schema, ''dbo'') -- Raises Error unless COLLATE used
, N'@Schema sysname'
, @Schema = @Schema