Sql-server – DB collation is used for comparison instead of column collation

Ok, so when you ran

SELECT * FROM sys.columns WHERE object_id = object_id('tb1')

for each of the tables you found that the collation was not the same as the database default. At this point you have two options.

Change the collation in the query

All you have to do here is use the COLLATE phrase after the column that needs it.

select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A' 
from DB2.dbo.tbl1 T2 
where T1.Number not in (select distinct AccountNumber COLLATE SQL_Latin1_General_CP850_CI_AI from DB1.dbo.tbl1) 

The above would force the AccountNumber column to use SQL_Latin1_General_CP850_CI_AI in this particular spot.

Change the collation of the columns

This could have ramifications elsewhere so you want to be careful before doing it but to change collation of a column it's usually just a simple ALTER TABLE ALTER COLUMN statement.

ALTER TABLE tbl1 ALTER COLUMN AccountNumber char(10) 
COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL

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