MS SQL Server - Linking tables, different database

Author

Linking tables, different database

zdrakec

2005-08-02, 9:23 am

Hello all:

I have two tables that I would like to perform a join on via a given
field. Easy enough, except that the tables exist in different
databases, on different servers.
Is there a way to so reference a remote server so that I can do:

SELECT table1.* FROM table1 INNER JOIN table2 ON (table2.fieldnamevalue
= table1.fieldnamevalue)

when table1 and table2 exist on different databases and different
servers?

Thanks much,
zdrakec

Narayana Vyas Kondreddi

2005-08-02, 11:23 am

Try these examples:

http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/

"zdrakec" <zdrakec@yahoo.com> wrote in message
news:1122994703.762256.162430@g47g2000cwa.googlegroups.com...
Hello all:

I have two tables that I would like to perform a join on via a given
field. Easy enough, except that the tables exist in different
databases, on different servers.
Is there a way to so reference a remote server so that I can do:

SELECT table1.* FROM table1 INNER JOIN table2 ON (table2.fieldnamevalue
= table1.fieldnamevalue)

when table1 and table2 exist on different databases and different
servers?

Thanks much,
zdrakec

Jens Süßmeyer

2005-08-02, 11:23 am

For different servers you have to setup a linked server 8look in the bol how
to do that), for just a different database can leave the first part of the
four point naming:

Select * from [servername].[databasename].[owner].[objectname] in your case

SELECT table1.* FROM table1 INNER JOIN
linkedServername..table2.fieldnamevalue table2 ON table2.fieldnamevalue
= table1.fieldnamevalue)

assuming that table1 is on your local server.

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"zdrakec" wrote:

> Hello all:
>
> I have two tables that I would like to perform a join on via a given
> field. Easy enough, except that the tables exist in different
> databases, on different servers.
> Is there a way to so reference a remote server so that I can do:
>
> SELECT table1.* FROM table1 INNER JOIN table2 ON (table2.fieldnamevalue
> = table1.fieldnamevalue)
>
> when table1 and table2 exist on different databases and different
> servers?
>
> Thanks much,
> zdrakec
>
>

zdrakec

2005-08-02, 11:23 am

Hello Narayana:

Well, I get a good link, but am unable to see any but system tables,
even though the table owner in all cases is the default (dbo). When I
attempt to query the remote table, I get the following lovely error
message (this is from Query Analyzer):

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: The stored procedure required to
complete this operation could not be found on the server (they were
supplied with the ODBC setup disk for the SQL Server driver). Please
contact your system administrator.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IDBSchemaRowset::Get
Rowset returned 0x80004005: ].

So... what am I missing?

Thanks much,
zdrakec

Sponsored Links