Link data between two databases

[JohnKIT05]

I know how to use the inner join command on linking tables within a database but how will be able to link data between two different databases? The code I have shown below is receiving an error which is

"Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo_ReceivingLog.receiving'."

I've changed the INNER JOIN several ways such as
INNER JOIN receiving but no luck. Again how can a link be done between two databases?


UPDATE PurchaseOrders
SET PurchaseOrders.RecDate = receiving.[appointment date]
FROM PurchaseOrders
INNER JOIN dbo_ReceivingLog.receiving
ON dbo_TrafficDaily.PurchaseOrders.PONumber = dbo_ReceivingLog.receiving.[po number]

 

[JohnDTampaBay]

A fully qualified table name takes the form Server.Database.Owner.Object. If the databases are on the same server, you can omit Server. and use the three part name. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnKIT05]

I removed the dbo_ from the INNER JOIN and ON and still it dosplays an error on the INNER JOIN. I also tried removing the database name which are TrafficDaily and ReceivingLog and no luck.

Should I include the USE statement. If yes, will it allow me to incluse two databases in a USE staement?

 

[Catadmin]

No, you can't use the USE statement with more than one database.

Have you tried changing:

UPDATE PurchaseOrders
SET PurchaseOrders.RecDate = receiving.[appointment date]
FROM PurchaseOrders...

to

UPDATE PurchaseOrders
SET PurchaseOrders.RecDate = (Select receiving.[appointment date] FROM PurchaseOrders
...)

I've always used the Select statement in an update such as you have. Never tried to a <fieldname> FROM <tablename> without one. Also, denote database name before *every* instance of the tablename.fieldname so SQL Server doesn't get confused as to what DB you are talking about. It's also cleaner code.





Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[JohnKIT05]

Catadmin,

That makes sense but it does not like the receiving object name. I checked to be sure it is spelled right. Show below the lastest code and error:

UPDATE PurchaseOrders
SET PurchaseOrders.RecDate = (Select receiving.[appointment date]FROM receiving)
FROM PurchaseOrders
INNER JOIN receiving
ON dbo_TrafficDaily.PurchaseOrders.PONumber = dbo_ReceivingLog.receiving.[po number]

ERROR: Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'receiving'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'receiving'.

I also tried again by removing the dbo_ on the last two lines but received the same error.

 

[Catadmin]

Remember, DB name has to come before every tablename or tablename.fieldname. Your syntax after the select statement for the join was also in the wrong order. It should all be a part of the Select statement, not outside of it.

Try this:

UPDATE TrafficDaily.PurchaseOrders
SET TrafficDaily.PurchaseOrders.RecDate = (Select ReceivingLog.receiving.[appointment date]FROM receiving inner join PurchaseOrders
INNER JOIN ON ReceivingLog.receiving.[po number] = TrafficDaily.PurchaseOrders.PONumber)

Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

YEESH! I forgot to delete some code. Try THIS instead of that above.

UPDATE TrafficDaily.PurchaseOrders
SET TrafficDaily.PurchaseOrders.RecDate = (Select ReceivingLog.receiving.[appointment date]FROM receiving inner join PurchaseOrders
ON ReceivingLog.receiving.[po number] = TrafficDaily.PurchaseOrders.PONumber)

Sorry about that....



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

And yet again, I don't follow my own advice. Let me try this one last time. Also, a quick note on the join. You might have to make it a Left Outer to get the data you're looking for.

UPDATE TrafficDaily.PurchaseOrders
SET TrafficDaily.PurchaseOrders.RecDate = (Select ReceivingLog.receiving.[appointment date]FROM ReceivingLog.receiving inner join TrafficDaily.PurchaseOrders
ON ReceivingLog.receiving.[po number] = TrafficDaily.PurchaseOrders.PONumber)

Okay. That really should do it.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[JamesLean]

Catadmin, you still have errors in your query - you need to include the owner between database and table name. In any case I think you're over-complicating here:

UPDATE p
SET RecDate = r.[appointment date]
FROM TrafficDaily.dbo.PurchaseOrders p
  INNER JOIN ReceivingLog.dbo.receiving r ON p.PONumber = r.[po number]

--James

 

[JohnKIT05]

Catadmin and Jameslean,

Thanks for your help!!!. I apprecaite your time in helping me out. Catadmin ( Sorry) I ended up using JamesLean code becuase I did not come across any errors and I learned alot from you both. But ..the search is not finding any same data. After some investigating, I believe it is the trailing spaces is causing the problem. I am using the RTRIM command right now but I am receiving errors. It does not like the last RTRIM as show the error below:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'po number


INNER JOIN ReceivingLog.dbo.receiving r
ON RTRIM(p.PONumber) = r.RTRIM([po number])
)

 

[JohnKIT05]

Catadmin and JamesLean,

I found there were a extra ')' shown. So I removed it and no errors. Still no data. I will have to do more investigating. Something is going on.

John

 

[Catadmin]

Have you tried changing your inner join to a left outer or right outer join?

Try each of them and see if that gives you any results.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

Shouldn't this:

INNER JOIN ReceivingLog.dbo.receiving r
ON RTRIM(p.PONumber) = r.RTRIM([po number])
)

be this:

INNER JOIN ReceivingLog.dbo.receiving r
ON RTRIM(p.PONumber) = RTRIM([r.po number])
)

Tim