What does this statement mean: SELECT … COLLATE SQL_Latin1_General_CP1_CI_AS AS [-‌- Logins To Be Created –]

All we need is an easy explanation of the problem, so here it is.

What does this statement mean?

SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]

It is part of a script I’m trying to adapt for listing out the SQL Server logins in an instance to be migrated into another instance. Here is the whole code:

 SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
               CASE 
                    WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
                        + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
                    ELSE ' FROM WINDOWS WITH'
                END 
       +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP 
LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
        AND SP.name NOT LIKE '##%##'
        AND SP.name NOT LIKE 'NT AUTHORITY%'
        AND SP.name NOT LIKE 'NT SERVICE%'
        AND SP.name <> ('sa')
        AND SP.name <> 'distributor_admin'

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The AS [-- Logins To Be Created --] is merely a column alias for the result set since what is being selected is an expression, not a column. Expressions do not have a column name by default, so this is a way of labeling that result set column for cleaner output.

If you execute that query you will see that the single column in the result set is labeled:
-- Logins To Be Created --.

This has nothing to do with collation in any way. The COLLATE SQL_Latin1_General_CP1_CI_AS is potentially extraneous and unnecessary. If I remove it (from the query posted in the question) the query works just the same. However, in some cases (for other queries, not this one) a COLLATE clause/keyword is needed to explicitly force a common collation across all columns being concatenated (which is happening in this particular expression) if there are multiple columns and not all columns being used in the concatenation (or expression in general) use the exact same collation. In that case, without the COLLATE {any_collation_should_work} then the query would get a collation conflict error.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply