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_
tracking has a view to a server that is remote (in a DMZ)
CREATE VIEW [dbo].[v_ShipmentTrackingNumbers]
FROM OPENDATASOURCE ('SQLOLEDB',
'Data Source=x.x.x.x;User ID=user;Password=thepassword' ).remotedb.dbo.ShipmentTrackingNumbers Rowset_1
DECLARE @TrackingNumberDeleteList TABLE (
FROM dbo.v_ShipmentTrackingNumbers a
INNER JOIN @TrackingNumberDeleteList b on a.TrackingNumber = b.TrackingNumber
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
IN and not Latin1_General_BIN