Collation conflict when running dynamic sql FOR XML PATH

Question

I am getting the following error message when running the query below:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in the equal to operation

This query works fine and basically gives me the scripts to CREATE USER in my current database.

In order to run the query in ALL (or a selection) of my databases in the current server, I have it done through dynamic SQL and for XML PATH.

The problem is, when I uncomment the XML part of the script below, I get the error above.

It is not in ALL servers that I get this error. Some servers work fine, others are bogged.

I want this query to work in ALL servers, independently of server collations.

How can I achieve this?

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);



SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''       
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']'' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name]=[master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

--SET @SQL = (
--          SELECT STUFF(
--  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
--                              FROM sys.databases SD
--                              --INNER JOIN @DBS D ON SD.NAME = D.DB
--                              WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
--                                AND SD.COMPATIBILITY_LEVEL > 80  

--    FOR XML PATH(''),TYPE)
--  .value('text()[1]','nvarchar(max)'),1,2,N'')
--)


EXECUTE MASTER.DBO.sp_executesql @SQL

HERE IS THE WORKING VERSION

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);



SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]= 
''CREATE USER [''       
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' COLLATE Latin1_General_CI_AS +  [master].[sys].[server_principals].[name] + '']'' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]  
on [sys].[database_principals].[name] COLLATE Latin1_General_CI_AS = [master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @SQL = (
            SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @SQL + @vCrlf    
                                FROM sys.databases SD
                                --INNER JOIN @DBS D ON SD.NAME = D.DB
                                WHERE SD.STATE_DESC = 'ONLINE' -->Skips the database if it is not online
                                  AND SD.COMPATIBILITY_LEVEL > 80  

      FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)


EXECUTE MASTER.DBO.sp_executesql @SQL

Answer 1

You'll need to put a COLLATE hint in your join clause. Basically, choose one of the two collations, i.e. Latin1_General_CI_AS and place it after the column name that uses the other collation.

SELECT @SQL = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''       
 + [sys].[database_principals].[name] + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] + '']'' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] **COLLATE Latin1_General_CI_AS** = [master].[sys].[server_principals].[name]
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')'

Answer 2

Another method would be to use COLLATE DATABASE_DEFAULT vs specifying the collation explicitly.

DECLARE @sql NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(MAX)

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);

    SELECT  @log = ''
           ,@vCrlf = CHAR(13)+CHAR(10);



SELECT @sql = '
--==========================================
-- creating the users
--==========================================

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
''CREATE USER [''       
 + [sys].[database_principals].[name] collate database_default + '']'' + '' FOR LOGIN ['' + [master].[sys].[server_principals].[name] collate database_default + '']'' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in (''U'', ''G'', ''S'')

'

SET @sql = (
          SELECT STUFF(
  (SELECT N'  ' + ' USE ' + QUOTENAME(name) +';' + @vCrlf + @sql + @vCrlf    
                              FROM sys.databases SD
                              --INNER JOIN @DBS D ON SD.NAME = D.DB
                              WHERE  SD.state_desc = 'ONLINE' -->Skips the database if it is not online
                                AND SD.compatibility_level > 80  

    FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')
)


--EXECUTE master.dbo.sp_executesql @SQL
print @sql

Above query will produce below print statement :

select  db = db_name(),
         _login=[master].[sys].[server_principals].[name],
         role_= null,
         obj=null,
        Permission= null,
        [script]=
'CREATE USER ['       
 + [sys].[database_principals].[name] collate database_default + ']' + ' FOR LOGIN [' + [master].[sys].[server_principals].[name] collate database_default + ']' 

from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals]
on [sys].[database_principals].[name] collate database_default =[master].[sys].[server_principals].[name] collate database_default
where [master].[sys].[server_principals].[type] in ('U', 'G', 'S')