September 18, 2014 at 5:35 am
Sorry it should have read "Select" not Join
I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price]
WHERE NOT EXISTS
(SELECT [LocalDB].[dbo].[Sales Price].*
FROM [LocalDB].[dbo].[Sales Price]
WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND
([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
September 18, 2014 at 9:29 am
Try with table aliases:
SELECT *
FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price] AS remotetable
WHERE NOT EXISTS
(SELECT localtable.*
FROM [LocalDB].[dbo].[Sales Price] AS localtable
WHERE (localtable.[Item No_] collate Latin1_General_CS_AS = remotetable.[Item No_]) AND
(localtable.[Unit of Measure Code] collate Latin1_General_CS_AS = remotetable.[Unit of Measure Code]) )
-- Gianluca Sartori
September 18, 2014 at 10:45 am
That did work, Thank You
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply