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

• Kin Shah: You have another problem with your Working version, make sure to have all variables same - lowercase or uppercase + MASTER.DBO. should be master.dbo .. see my answer .. as the script will fail on Binary collation servers as it is case sensitive.
• Marcello Miorelli: @Kin that's very true. it did not take long for me to figure that out... the hard way

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'')'

• tommyfly: Obviously, remove the asterices from the script
• Marcello Miorelli: that worked, but now I got it somewhere else. it says: Cannot resolve collation conflict for column 6 in SELECT statement
• Kin Shah: See my answer below .. that is complaining about [script] column ... you need [script]= 'CREATE USER [' + [sys].[database_principals].[name] collate database_default + ']' + ' FOR LOGIN [' + [master].[sys].[server_principals].[name] collate database_default + ']'
• tommyfly: Aahh, I was too fast. Missed the SELECT statement. Sorry.

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')

• Marcello Miorelli: using COLLATE DATABASE_DEFAULT also worked fine