Common Collation Conflict

Whenever you access database schema outside the scope of the current database there is a chance that you will run into a collation conflict error. Most of the instances of this error that I have encountered have been caused by remote databases using the older SQL collation (SQL_Latin1_General_CP1_CI_AS) instead of the windows collation (Latin1_General_CI_AS).

Because these two collations use the Latin1 (ANSI) code page and have the same sort orders (CI_AS), there is no harm in changing from one to the other in a conflict scenario.

Consider the following query:

SELECT cur.[Column1]
FROM [dbo].[Table1] AS cur
INNER JOIN [RemoteServer].[RemoteDb].[dbo].[Table1] AS rem
ON (rem.[Column1] = cur.[Column1]);

This produces the following error because the remote table column has been defined using the SQL collation.

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

To resolve this problem, we could simply add the COLLATE statement to convert to the local Windows collation.

SELECT cur.[Column1]
FROM [dbo].[Table1] AS cur
INNER JOIN [RemoteServer].[RemoteDb].[dbo].[Table1] AS rem
ON (rem.[Column1] COLLATE Latin1_General_CI_AS = cur.[Column1]);

However, there is another way; a linked server can be configured to ignore remote collations by changing the "Use Remote Collation" Server Option to FALSE. This is a far cleaner, simpler way to handle collation conflicts where linked servers are involved.

Microsoft buys DATAllegro

Firstly, I don’t know much about DatAllegro, other than the fact that their product is an enterprise Data Warehouse solution. A colleague of mine told me about the acquisition (http://www.datallegro.com/pr/7_24_08_microsoft_acquisition.asp) this morning. What I think is significant is that MS is continuing to grow its database platform and that is good news for SQL Professionals. The way I look at it is that if SQL grows, I will grow with it and somewhere down the line my job description and skill-set become far more valuable. That can only be a good thing.

To Blog or not to Blog

I have been convinced by one my colleagues, Matt (http://cubisbi.blogspot.com/) that it would be a good idea to start a blog for a wider audience than our corporate environment; this after posting internally for nearly a year now.

My focus is SQL Server and Visual Studio for Database Pro's.

Hope you get some sort of value from the blog.

Cheers

BG