Joining Tables From Diff Databases

[Strider]

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?

[Dennis DVR]

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.

[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.

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

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

[techgnome]

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.

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

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

[brucevde]

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 *******)

[Shevy]

how do you set up a linked server?