1

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
  • 1
    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. – Kin Shah Jun 4 '15 at 15:52
  • @Kin that's very true. it did not take long for me to figure that out... the hard way – Marcello Miorelli Jun 12 '15 at 9:51
3

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'')'
  • 1
    Obviously, remove the asterices from the script – tommyfly Jun 4 '15 at 15:12
  • that worked, but now I got it somewhere else. it says: Cannot resolve collation conflict for column 6 in SELECT statement – Marcello Miorelli Jun 4 '15 at 15:16
  • 1
    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 + ']' – Kin Shah Jun 4 '15 at 15:21
  • 1
    Aahh, I was too fast. Missed the SELECT statement. Sorry. – tommyfly Jun 4 '15 at 15:22
5

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')
  • using COLLATE DATABASE_DEFAULT also worked fine – Marcello Miorelli Jun 4 '15 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.