Problem with UPDATE to another server.

Hi Arnie!

Thanks for the info!! So I got that join to work, but now I need to take that data from "SAHTest" and update Pharm Local Test. I'm not sure what is the best and quickest way. Do I make a temp table, or can i grab it from "inserted"? Any way, here was my shot.

DECLARE @.Acct nvarchar(50)

SET @.Acct ='L1234'

BEGIN

SELECT x.account, x.Fname, x.lname, x.dob

FROM [MKE01-Demo-01].SAHPharm.dbo.testact AS x JOIN

[Pharm Test Local].dbo.Active_Orders AS t ON t.Account_Number = x.account

WHERE(x.account = @.Acct)

END

BEGIN

UPDATE [Pharm Test Local].dbo.Active_Orders AS t

SET Account = t.Account_Number, Fname = t.First_Name, LName = t.Last_Name,

dob = t.DOB

FROM [MKE01-Demo-01].SAHPharm.dbo.testact

WHERE [MKE01-Demo-01].SAHPharm.dbo.testact.account = t.Account_Number

End

Any ideas?

Thanks!

Rudy

You should be able to combine the two queries into one.

Code Snippet

UPDATE [Pharm Test Local].dbo.Active_Orders
SET
l.Account = t.Account_Number,
l.Fname = t.First_Name,
l.LName = t.Last_Name,
l.dob = t.DOB
FROM [Pharm Test Local].dbo.Active_Orders l
JOIN [MKE01-Demo-01].SAHPharm.dbo.testact t
ON l.Account_Number = t.Account
WHEREt.Account_Number = @.Acct

|||

Hi Arnie!

Thank you for the relpy. I tried your above solution, but it doesn't work. It seems I can't use JOIN on a SQL 2005 box. My Local server is SQL 2005, and SQL 2000 is on my target. So I'm trying to figure out the new OUTER APPLY that I discovered. I'm getting this error when I run the above statement in you post,

"

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "l.Account_Number" could not be bound."

I'll keep you updated in my findings, but if there is anybody who can enlighten me, I'm all ears!

Thanks!

Rudy

|||

It seems I can't use JOIN on a SQL 2005 box

Now that totally doesn't make sense. JOIN is so basic to SQL, it's like air, you can't exists without it. ALL versions of SQL Server support JOIN.

So I'm trying to figure out the new OUTER APPLY that I discovered

NOW that definitely will not work on a SQL 2000 server.

If you would carefully examine the statement I suggested, you would see that I made a transposition error in this line:

ON l.Account_Number = t.Account

I believe it should be: ON l.Account = t.Account_Number instead.

|||

Hi Arnie!

I did catch that. I have another guy here baffled as well. He told me that the JOIN will work, I'm just not sure why I'm getting that error. I'll keep plugging away.

Thanks

Rudy

|||

Hello Arnie!

After a little digging, I figured it out. Heres what I got!

Thanks again for all your help on this!

Rudy

UPDATE [Pharm Test Local].dbo.Active_Orders

SET

Account_Number = t.Account,

First_Name = t.Fname,

Last_Name = t.Lname,

dob = t.DOB

FROM [MKE01-DEMO-01].SAHPharm.dbo.testact t

WHERE Account_Number = t.Account