Collation conflict using 2 versions of SQL Server

collation, sql, sql-server, sql-server-2008

Solution

not exactly sure what you need, but if its only collation issue you can do below

SELECT TOP 5 * 
FROM   [192.168.6.3].sync.dbo.agents_log
WHERE  member_code COLLATE SQL_Latin1_General_CP1_CI_AS 
IN (SELECT id
    FROM   members
    WHERE  NAME LIKE 'AOne Enterprises%')

I just added COLLATE SQL_Latin1_General_CP1_CI_AS , perhaps it work

Problem

I am working on 2 versions of SQL Server i.e 2005 and 2008 R2. From 2008 R2 I have created a linked server which will connect to an older SQL Server 2005 instance. I have one table on my server (2008) which is as below members ``` id name 0002320 AOne Enterprises Motihari 0002321 AOne Enterprises Siliguri ``` Another table which resides on remote server contain activity of each agent ``` id member_code agent rr_no txn_date amount ``` I fired a query below ``` select top 5 * from [192.168.6.3].sync.dbo.agents_log where member_code IN (select id from members where name like 'AOne Enterprises%') ``` I was trying to pull all activity log of AOne Enterprises through out the country which is in distributed database , so I need to create a link servers. I got 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_BIN" in the equal to operation.

Original source