Sql-server – How to connect to a database which is a linked server via ODBC

linked-serverodbcsql serversql-server-2008

(All the searches for this sort of thing tell me about where the linked server is connecting to the original database via ODBC, but I'm connecting to the intermediate server via ODBC.)

I have a Python script which I want to connect to an Oracle database. In between the two I have a server running Server 2003 and SQLServer 2008. I have created a linked database on this intermediate server which successfully connects to the Oracle server. (Using Management Studio I can run a query on the remote Oracle server successfully.)

When I set up the machine that the Python script is running on, (happens to be another Server 2003, but I'll be running it on XP and Win7 machines too), I try to create an OBDC data source to the intermediate server. When the list of databases on the server comes up it doesn't list the linked server databases, only the normal databases on that intermediate server.

Is it possible to get ODBC to look at a linked server database? If so, how?

Best Answer

You have created a linked server, not a linked database, so there is no list of databases to select from. You need to either use a "four-part name" or use the OPENQUERY() function.

Four-part names describe an object on another server. Usually a four-part name would look like "servername.databasename.schemaname.tablename", but for Oracle you apparently need to leave the database name out. A query using a four-part name for Oracle might look like this:

SELECT name FROM OracleSvr..joe.titles WHERE id = 101

where "OracleSvr" is the name of the linked server.

Alternatively, here is an example of a statement that uses OPENQUERY. It should do the same thing, some people find this style easier to read.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

OPENQUERY is documented here.

No matter which style of query you choose, you would connect via your Python program to the SQL Server and then run the query.

You can do this via ODBC, you don't need to specify the database name when setting up the connection, you do it at query time as described above.

Related Solutions

Postgresql – limit access to postgresql 8.4 databases via ODBC on the server side

ODBC is a client tool and does not affect the way that Postgresql verifies access. I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install. In Linux, you typically find it in /var/lib/pgsql/data/.

This file allows you to define which ip addresses can access your postgresql server and how they may access it. Types of access include trust (no password), md5 (password), etc.

So for example, if your access users are on say 192.168.0.10 and you want them to use a password you would add this entry.

# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         access_user         192.168.0.10    255.255.255.255     md5

In postgresql you would then have to create the user and give him the ability to login.

1. psql -d nameofdatabase
2. CREATE ROLE access_user WITH LOGIN PASSWORD 'jw8s0F4';
3. GRANT SELECT ON nameoftable TO access_user;

Good luck

Sql-server – Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

If I try to right click on a view name and select Script -> SELECT To.... I get a message

The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.

Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.

Best Answer

Related Solutions

Postgresql – limit access to postgresql 8.4 databases via ODBC on the server side

Sql-server – Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

Related Question