Collation Issues with SQL 2008

I have a SQL 2008 R2 server with a default collation method of Latin1_General_BIN

I have attached a database from a SQL 2000 server. Lets call it tracking.

tracking has a collation method of SQL_Latin1_General_CP1_CI_AS

tracking has a view to a server that is remote (in a DMZ)

CREATE  VIEW [dbo].[v_ShipmentTrackingNumbers]
AS
SELECT     *
FROM         OPENDATASOURCE ('SQLOLEDB', 
                      'Data Source=x.x.x.x;User ID=user;Password=thepassword' ).remotedb.dbo.ShipmentTrackingNumbers Rowset_1

Open in new window



DECLARE @TrackingNumberDeleteList TABLE (
		ShipmentID int,
		TrackingNumber varchar(20)
		) 
		 	
SELECT *  
FROM dbo.v_ShipmentTrackingNumbers a
INNER JOIN @TrackingNumberDeleteList b on a.TrackingNumber = b.TrackingNumber

Open in new window


results in

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

Why is the default collation method of the database coming into play here? The tracking database uses the same collation as the remote database?

This code works on SQL 2000 although that server has a default collation method of
SQL_Latin1_General_CP850_BIN and not Latin1_General_BIN

spencerturbineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
Try this

DECLARE @TrackingNumberDeleteList TABLE (
                ShipmentID int,
                TrackingNumber varchar(20)
                )
                       
SELECT *  
FROM dbo.v_ShipmentTrackingNumbers a
INNER JOIN @TrackingNumberDeleteList b on a.TrackingNumber  = b.TrackingNumber collate SQL_Latin1_General_CP1_CI_AS

0
Kevin CrossChief Technology OfficerCommented:
You have not specified a collation in the table variable; therefore, I would expect it to take the default collation of the server. Try specifying the collation in the definition of TrackingNumber within @TrackingNumberDeleteList OR try using the COLLATE operator http://msdn.microsoft.com/en-us/library/ms184391.aspx within the comparison.
0
Kevin CrossChief Technology OfficerCommented:
That is what I get for NOT refreshing.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

spencerturbineAuthor Commented:
I appreciate the responses but here is the problem. I don't want to have to re-code 7 years worth of SQL. I am trying to understand what has changed that causes my current code to operate differently.

Its interesting that if I create a linked server and then change the view to go through the linked server I don't have this issue. It's only any issue when creating the veiw using OPENDATASOURCE

I will recode all the  views using a linked server before I add collate statements to all the columns throughout the SQL code.



0
TempDBACommented:
Using the linked server options is the only way to enable using remote collations. Therefore, queries that are constructed that use ad hoc names provided by OPENROWSET and OPENDATASOURCE cannot use the collation information of remote character data. Additionally, all linked servers in SQL Server version 7.0 that are upgraded to SQL Server 2000 or later are set to use remote collation = false.

Reference:-http://msdn.microsoft.com/en-us/library/ms191145.aspx

It is always recommended to use link server than other way of using distributed queries. Its a wise decision to change the views and other codes I guess.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spencerturbineAuthor Commented:
Yea I just like how it was very centralized with the views totally self contained. I didn't need to worry about creating linked servers on what ever server I moved my stored procedures and views to.

0
spencerturbineAuthor Commented:
Pretty much exactly what I was looking for at that link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.