Linked SQL Servers and ODBC

[CylonLove4Life]

I'd like to set up an ODBC DSN to a table in a linked SQL Server, via my local SQL Server.

I'm having a few problems:
1. When I use Enterprise Manager to link the remote SQL Server, it doesn't allow me to select the database in the remote server. It only shows one database.

2. When I use the Data Sources program to set up the ODBC DSN, it only shows databases in the local SQL Server, not in the linked one. If I try typing it in: [NAMEOFLINKEDSERVER].databasename - it tells me that it is an invalid table.

 

[CylonLove4Life]

I'd really appreciate it if someone could help me get started with this. If my post is unclear or you need more info, just let me know!

 

[mrdenny]

When you list the tables in a linked server in Enterprise Manager it only shows you the default database of the user on the remote server.

You can't query a database, you have to query a table. To query a table on the remote server you have to use the full four part name of the object.

select *
from ServerName.DatabaseName.Owner.Object

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[CylonLove4Life]

Right, but my question is:

Using the ODBC Adminsitrator, you can choose a default database for the Data Source that you are creating.

I'd like the default database to be a databse in the linked server - but the ODBC Administrator won't allow this.

 

[mrdenny]

Your default database has to be on the server you are connecting to. Your default database can't be on a remote server as you don't have direct access to that database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com