Problem with select to another server.

Hello All!

This is the first time I'm working between two SQL servers. Here is a simple SELECT statement.

DECLARE @.Acct nvarchar(50)

SELECT First_Name, Last_Name, Age, DOB, Account_Number

FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

Here is the error

Msg 4104, Level 16, State 1, Line 4

The multi-part identifier "MKE01-2NX2461.Pharm Test Local.dbo.Active_Orders" could not be bound.

What am I doing wrong?

Thanks!

Rudy

MKE01-2NX2461 is set up as a Linked Server in MKE01-Demo-XX?

|||

You are attempting to compare a table to a variable.

WHERE [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders = @.Acct

It would be nice, but it isn't going to happen...

But more critically, I don't see any relationship between the two tables from the two databases. How would a row from [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders be connected to the table [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders in such a way that using the variable value against one table 'should' return a row from the other table.

I think that something isn't quite right here... (Perhaps a JOIN is missing.)

|||

Hi guys!

Ah yes. A JOIN would make sense. Mke Demo is the linked serve on MKe 29nx... So let me give that a shot. I'm sure I'll be back here with a question or two.

Thanks!

Rudy

|||

Perhaps something more like this?

Code Snippet

DECLARE @.Acct nvarchar(50)

SET @.Acct = {someValue}

SELECT
x.First_Name,
x.Last_Name,
x.Age,
x.DOB,
x.Account_Number
FROM [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders x
JOIN [MKE01-2NX2461].[Pharm Test Local].dbo.Active_Orders t
ON x.Account_Number = t.Account_Number
WHERE x.Account_Number = @.Acct

This assumes that the values you wish to return are located in the [MKE01-Demo-XX].SAHPharm.dbo.Active_Orders table, and that both tables have the Account_Number column to link the data.