ODBC Connection to a linked database

[listrecv]

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.

[Jens]

Thats quite normal, but you are able to use the four part notation,
which will switch to another database if specified.

E.g. ODBC Connection points automatically to Northwind ( as the default
database of the user) you can use

SELECT <columnlist> FROM Linkdservername.Databasename.Objectname

which switches to the other database to select.

HTH, Jens Suessmeyer.

[listrecv]

Jens,

Thank you.

I'd rather not need to change the table names in the MDB. They just
use the default database of the ODBC Data Source. How can I set the
ODBC default database to be a database in a linked server?

[Jens]

if you are using a DSN, just go in the ODBC Administrator (of Windows)
and change the database to the ones needed.

HTH, jens Suessmeyer.

[listrecv]

Jens,

Thanks - but the ODBC Adminsitrator only shows *local* databases, not
databases on the linked server. If I try to just type it in, it tells
me that it's not a valid db, and refuses to let me do so!!!

[Jens]

How did you register the linked server ? Is it a DSN bind linked server
or was it specified within a conneciton string ? ODBC Administrator
doesn´t show only the local databases, it shows the servers/databases
that were specified within the DSN. Look at the server ODBC Admin to
see if connection to your linked server is specified (some people look
on their local DSN rather than looking on the remote DSNs onthe SQL
Server which uses the linked server)

HTH, Jens Suessmeyer.