Free SQL Server Learning - Making the most out of SQL Server Agent
solving sql server problems for millions of dbas and developers since 2006


SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups SQL Server Events I am MSSQLTips MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page









Top SQL Server Tools






































Handling cross database joins that have different SQL Server collations

By:   |   Read Comments (2)   |   Related Tips: More > JOIN Tables

Problem
Recently I had to create a SQL Server instance with a non-standard collation (or as the vendor that created the database solution we were implementing called it "non-default", which gave me a chuckle that they were arguing the matter.)  Now that the collation for the instance is Latin1_General_BIN none of my managerial scripts are working. This is causing a great deal of difficulties in our standardized maintenance jobs we've created for such items as backups, statistics updates, and indexing rebuilds and reorganizations.  Is there an easy way to resolve collation issues when the server collation is different than the collation on one or more of the user databases on an instance?

Solution
It sounds as though you have a similar process for customizing maintenance tasks as I do.  Most DBAs realize that the built in Maintenance Plans within SQL Server are good for the beginning Junior DBA or the "Mom-and-Pop" shops that don't have an IT department, but for enterprise systems, they simply do not hold up.  We too have a system for maintenance tasks that rely on a user database for storing exception information; which databases shouldn't I backup, run indexing maintenance on, so-on-and-so-forth.  I compare the contents of these "ignore tables" as I refer to them against the sys.databases system catalog table and then only apply the maintenance process to the database names list that result from the sub query of the two objects.  Let's take a quick look at my scenario.  Given the following environmental conditions:

The SQL instance in question has the following databases:

SELECT [name] 
FROM sys.databases 
ORDER BY [name]

 

And the records for the iDBA.backupBOT.ignore_databases table look like this:

SELECT [name] 
FROM iDBA.backupBOT.ignore 
ORDER BY [name]

The simple query below will provide a listing of the databases that will be backed up by the process I use on my SQL Server 2005 instances:

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm  
   FROM iDBA.backupBOT.ignore_databases
   )

This is of course only the case where the collations between the system databases and the iDBA database are compatible.  However, what happens when you have a situation like what you're experiencing?  I've taken the time to create a new SQL Server instance with a collation of Latin1_General_BIN.  I've also restored a copy of my iDBA database to this instance.  A simple query against sys.databases gives some insight into the collation situation on the instance:

SELECT [name], [collation_name] 
FROM [master].sys.databases 
WHERE [name] = 'master' OR [name] = 'iDBA' 
ORDER BY [name]

We'll proceed by running the same query that produced results for listing the names of databases to backup as we did on our other SQL Server instance.  However this time we'll get an error that probably looks familiar to you now at this point:

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm  
   FROM iDBA.backupBOT.ignore_databases
   )

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

What proves to be interesting is that I can convert the collation of the iDBA database to match the instance collation and still receive the same error:

ALTER DATABASE iDBA
COLLATE Latin1_General_BIN

SELECT [name], [collation_name] 
FROM [master].sys.databases 
WHERE [name] = 'master' OR [name] = 'iDBA' 
ORDER BY [name]
 

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm  
   FROM iDBA.backupBOT.ignore_databases
   )

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

Furthermore I have tested out a process by which I create a new database, using the server collation of SQL_Latin1_BIN; then using the SELECT...INTO code structure, create the ignore_databases table and populate it from iDBA (using the incompatible collation).  We still receive a error due to collation conflict:

CREATE DATABASE [iDBA2] ON  PRIMARY 
   (NAME = N'iDBA2', FILENAME = N'D:\Data\iDBA2.mdf' , SIZE = 5MB , FILEGROWTH = 5MB    )
   LOG ON 
   (NAME = N'iDBA2_log', FILENAME = N'E:\Logs\iDBA2_log.ldf' , SIZE = 3MB , FILEGROWTH = 3MB)
GO

USE [iDBA2]
GO
CREATE SCHEMA backup_BOT AUTHORIZATION dbo;

SELECT database_nm INTO iDBA2.[backupBOT].ignore_databases 
FROM iDBA.[backupBOT].ignore_databases;

SELECT [name], [collation_name] 
FROM [master].sys.databases 
WHERE [name] = 'master' OR [name] = 'iDBA' 
ORDER BY [name]

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm  
   FROM iDBA.backupBOT.ignore_databases
   )

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

Finally, there are two solutions to this problem.  The first involves a new database, creation of new database objects and population of the database tables in question with new data that matches the existing data in your other, non-compatible collation databases:

CREATE DATABASE [iDBA3] ON  PRIMARY 
   (NAME = N'iDBA3', FILENAME = N'D:\Data\iDBA3.mdf' , SIZE = 5MB , FILEGROWTH = 5MB    )
   LOG ON 
   (NAME = N'iDBA3_log', FILENAME = N'E:\Logs\iDBA3_log.ldf' , SIZE = 3MB , FILEGROWTH = 3MB)
GO

USE [iDBA3]
GO
 
CREATE SCHEMA backup_BOT AUTHORIZATION dbo;
CREATE TABLE [backupBOT].[ignore_databases]([database_nm] VARCHAR(50) NOT NULL) ON [PRIMARY];

INSERT INTO [backupBOT].[ignore_databases] (database_nm) VALUES ('tempdb');
INSERT INTO [backupBOT].[ignore_databases] (database_nm) VALUES ('iDBA');
INSERT INTO [backupBOT].[ignore_databases] (database_nm) VALUES ('Northwind');
INSERT INTO [backupBOT].[ignore_databases] (database_nm) VALUES ('pubs');

I can then run either of the following queries to obtain the correct results:

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm  
   FROM iDBA.backupBOT.ignore_databases
   )

--or

SELECT SD.name 
FROM master.sys.databases SD LEFT JOIN iDBA3.backupBOT.ignore_databases ID ON SD.NAME = ID.database_nm
WHERE ID.[database_nm] IS NULL 

will now yield the following results.

As I said, there are two options.  This first option is quite a pain:  creation of a new database, creation of new objects, manually inputting new values?  That is ridiculous and there must be a better way.  In fact, there is.  It requires use of the COLLATE keyword.  The COLLATE keyword, in the context of a SELECT statement, allows you to cast the collation of a column just as you would use CAST function to alter the implied data type of a column.  The following query casts the output for the [name] column into the Latin1_General_BIN collation so it can then be compared to the values in sys.databases.

SELECT SD.name 
FROM master.sys.databases SD 
WHERE [name] NOT IN 
   (
   SELECT database_nm COLLATE Latin1_General_BIN 
   FROM iDBA.backupBOT.ignore_databases
   )

 

This process requires no additional objects or databases to be created.  Furthermore all you are changing in the initial SELECT statement is the addition of the COLLATE keyword and collation name.  It is a very lightweight (and quick) change that has your administrative functions running as expected in no time.

Next Steps



Last Update: 3/27/2009

About the author

Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


Print  
Become a paid author


Comments and Feedback:

Sunday, March 29, 2009 - 11:54:04 PM - Henrik Staun Poulsen Read The Tip

I prefer to use  

COLLATE DATABASE_DEFAULT  

rather than COLLATE Latin1_General_BIN so that when we fix the collation, we will use the correct indexes without changes to the code.

Best regards,

Henrik Staun Poulsen

Stovi Software

 

Monday, March 30, 2009 - 1:36:03 PM - timmer26 Read The Tip

Very good idea Stovi, and that would work too so long as you are joining back to the master or another system database, which was my example.  Say however that you have DB1 that is the server default collation, DB2 that is collation X and DB3 that is collation Y.  if joining between DB2 and DB3 you would need to specify the collation.  Thanks for taking the time to present that viable solution as well.


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter

Comments
*Enter Code refresh code


 
Sponsor Information
"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA.

The COMPLETE Performance Solution for SQL Server - SQL Sentry

SQL Server Data Tools - Got questions? Get the answers here!

Copyright (c) 2006-2013 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.

Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com