Joining Tables From Diff Databases

Hi there,

i have 2 databases one and MSDE and the other a SQL SERVER database.

I would like to be able to join a table in my MSDE table (Stock) to a table in the SQL Server(Products)

how is this possible, or is it?

can this be done as a stored procedure?

If you're just going to do this only once you can use the OPENROWSET or OPENDATASOURCE look at your SQL Server Books Online regading these functions.

Should be possible. I have only ever done this from SQL Server to SQL Server. There are two methods.

Setup a Linked Server. Then you can use the 4 part name syntax to access the server.

Code:

---

Select *
From LocalTable L Left Join LinkedServerName.DataBaseName.OwnerName.TableName LS
On L.Id = LS.Id

---

Use the Ad-Hoc query methods - OpenRowset, OpenDataSource, OpenQuery

Code:

---

SELECT Stock.*, P.ProductName
FROM Stock
INNER JOIN OPENROWSET('SQLOLEDB','servername';'sa';'sapwd',
  'SELECT ProductId, ProductName FROM Products') AS P
  On Stock.ProductId = P.ProductId

---

Quote:

---

Originally Posted by brucevde

Should be possible. I have only ever done this from SQL Server to SQL Server. There are two methods.

Setup a Linked Server. Then you can use the 4 part name syntax to access the server.

Code:

---

Select *
From LocalTable L Left Join LinkedServerName.DataBaseName.OwnerName.TableName LS
On L.Id = LS.Id

---

Use the Ad-Hoc query methods - OpenRowset, OpenDataSource, OpenQuery

Code:

---

SELECT Stock.*, P.ProductName
FROM Stock
INNER JOIN OPENROWSET('SQLOLEDB','servername';'sa';'sapwd',
  'SELECT ProductId, ProductName FROM Products') AS P
  On Stock.ProductId = P.ProductId

---

---

Actualy, doesn't the Linked Server superceede the need to use the 4 part name? I've know I've gone from Server A to Server B using the 4 part harmony, with out a linked server.... I thought a Linked Server wasa "shortcut" of sorts, allowing you to LS.Table .... ?? but it's been a while, so I could be smoking something...

;P

Tg

You are smoking something again (and probably drinking too). ;)

If you leave out the server name, SQL Server assumes the current server. But then again things change so fast, I am becoming obsolete (so says the junior prog at work - l'il *******)

how do you set up a linked server?