0

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.

Nathan DeWitt
  • 6,511
  • 8
  • 46
  • 66

1 Answers1

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).

Gary Myers
  • 34,963
  • 3
  • 49
  • 74