SQL Server - query between 2 different servers (could not be bound)

Question

My Aim

to find a certain field from 1 servers database table in the other servers database table

The Problem

using the below event, I am unable to use WHERE/AND as the table wants a join, however when I implement the join in, it states "could not be bound" even when there is a field with a common name

Below is the query I am using tailored to make it generic

SELECT 
      CustomerDatabase.dbo.OldCustomerTable.CustomerID,
      CustomerDatabase.dbo.OldCustomerTable.FirstName,
      CustomerDatabase.dbo.OldCustomerTable.Surname,
      CustomerDatabase.dbo.OldCustomerTable.DOB
FROM 
      CustomerDatabase.dbo.OldCustomerTable

INNER JOIN 
      [REMOTESERVERNAME\CustomerDatabaseNEW].CustomerDatabaseV2.dbo.NewCustomerTable ON 
      CustomerDatabase.dbo.OldCustomerTable.CustomerID = 
      [REMOTESERVERNAME\CustomerDatabaseNEW].CustomerDatabaseV2.dbo.NewCustomerTable.CustomerID

WHERE
      CustomerDatabase.dbo.OldCustomerTable.DOB = [REMOTESERVERNAME\CustomerDatabaseNEW].CustomerDatabaseV2.dbo.NewCustomerTable.DOB

I have only added one line in the new table, so for example:

firstname : John

surname : Smith

DOB : 1990-01-20 (20th January 1990)

and using this information the query above should only return values that have the DOB: 1990-01-20

Tables:

CustomerDatabase (old)

  CREATE TABLE OldCustomerTable (CustomerID int identity (1,1) NOT NULL,
  FirstName varchar(50),
  Surname varchar(50),
  DOB Date)

  alter table OldCustomerTable add constraint CustomerID_PK PRIMARY KEY (CustomerID)

CustomerDatabaseNEW (new)

  CREATE TABLE CustomerTableV2 (CustomerID int identity (1,1) NOT NULL,
  FirstName varchar(50),
  Surname varchar(50),
  DOB Date)

  alter table CustomerTableV2 add constraint CustomerID_PK PRIMARY KEY (CustomerID)

I have Linked both databases together using sp_addlinkedserver and can successfully run a query such as:

SELECT * FROM [REMOTESERVERNAME\CustomerDatabaseNEW].CustomerDatabaseV2.dbo.NewCustomerTable

any pointers would be very helpful of how to solve this issue, and thank you for your help? I know the CustomerID WILL be different in the new table however does this matter? or is it possible to join on the DOB?

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Edit: just tried this with and without aliases and you need the aliases for it to work, hope it helps.

Try adding an alias for the tables in your join, possible duplicate of this question

SELECT 
      CustomerDatabase.dbo.OldCustomerTable.CustomerID,
      CustomerDatabase.dbo.OldCustomerTable.FirstName,
      CustomerDatabase.dbo.OldCustomerTable.Surname,
      CustomerDatabase.dbo.OldCustomerTable.DOB
FROM 
      CustomerDatabase.dbo.OldCustomerTable old

INNER JOIN 
      [REMOTESERVERNAME\CustomerDatabaseNEW].CustomerDatabaseV2.dbo.NewCustomerTable new ON 
      old.CustomerID = 
      new.CustomerID

WHERE
      old.DOB = new.DOB

i never found this post, thanks for your help and ill request this to be deleted as it is a duplicate — Crezzer7, Jun 26 '15 at 10:33

SQL Server - query between 2 different servers (could not be bound)

1 Answers1