'sql database collation mismatch

I am new to SQL and I am trying to query two separate databases using one script. However, both databases have different collations. I have tried to force the collation to change by running a script like this:

use master
go
alter database database1
collate SQL_Latin1_General_CP1_CI_AS
go

This does run but then it makes no difference when I try to run this query (even though both databases are now using SQL_Latin1_General_CP1_CI_AS):

select [Manufacturer],[ModelNumber] from database1.dbo.CVStore_Products inner join database2.dbo.tProduct on [StoreProductCode]=[ModelNumber]

What am I doing wrong? Because I keep getting this error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Thanks for your help in advance! :D

sqlsql-server


Solution 1:[1]

Collation can be a pain. But is has been around for quite a while. Here's a post from Pinal Dave, dating from 2007.
It has examples to either correct the collation in the query directly or to alter the collation for a specific column.

Changing the collation setting for an entire database, does not affect existing columns, as far as I can recall.
Any new columns will use the new setting.

Solution 2:[2]

OP note says he dropped the database.

Note to future users:

Options:

  1. Update tSQL query
  2. Update collation column in table
  3. Update collation of database
  4. Update collation of SQLServer master database

Update tSQL query**

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Update collation column in table

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

Update collation of database

SQL Docs say:

USE master;  
GO

ALTER DATABASE MyOptionsTest  
    COLLATE French_CI_AS ;  
GO  
  
SELECT name, collation_name  
FROM sys.databases  
WHERE name = N'MyOptionsTest';  
GO

Update collation of SQLServer master database

SQL Docs say:

  1. Export all your data
  2. Drop all the user databases.
  3. Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName

Warning:

When the master, model, msdb, and tempdb system databases are rebuilt MS Docs: Rebuild System Tables

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jacco
Solution 2 OzBob

Related Questions