Query to Linked Server never stops executing

Question

I have created a linked server in SQL Server 2005 to an Oracle DB. When I run a query, The query never stops executing, and never returns results. When I cancel the query, it never completes cancelling. I have to close the window to get it to stop.

I have set things up as follows:

• Installed Oracle Client Tools on SQL Server

• Ran following query:

  EXEC sp_addlinkedserver
@server = 'MyNewLinkedServer',
@srvproduct = 'Oracle',
@provider = 'OraOLEDB.Oracle',
@datasrc = 'TNSNAMES_Entry'

• I then added the TNSNAMES.ORA file to ORAHOME1\network\admin\ directory.

• In Properties for my Linked Server, I changed the Security settings to Be made using this security context: for all connections using my Oracle username/password

• I ran the following query:

  SELECT * FROM OPENQUERY(MyNewLinkedServer, 'SELECT COUNT(*) FROM MySchema.MyTable');

The query never completes execution. Anyone have any insights? Any steps I'm missing?

UPDATE:
I came in the following day and tried the query again and it worked just fine. I suspect network issues of some sort.

Answer 1

Look on the Oracle server, querying v$session. See if you can see the remote connection, and what the Oracle session is doing. You can even do a trace on the Oracle side (set off by a login trigger) to record everything that happens (eg parse of query, returned errors etc).