DB Link from Oracle to SQL Server

• SHAILAJA_SOMISETTY

  SSC Journeyman

  Points: 82

  More actions

  December 1, 2008 at 6:59 am

  #198242

  Hi,

  I have created a DB Link 'testsqlserver' from Oracle to Sql Server.

  I am doing a query from a view vcustomers which is the Sql Server database:

  SELECT * FROM vcustomers@testsqlserver.

  I am doing this query from TOAD.

  When I close the TOAD session, it asks me:

  ********************************************************

  ' A session that has pending transactions is about to close'

  Commit or Rollback.

  ********************************************************

  I am unable to understand why this is happening as I am just doing a select query.

  Please advise.

  Thanks & Regards,

  Shailaja

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  December 1, 2008 at 10:38 am

  #905761

  Toad is pretty bad managing sessions, it usually leaves connections open.

  My first suggestion would be to test the connection from SQL*Plus.

  If the issue persists try issuing a commit even if your transaction is only selecting.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• gints.plivna

  Ten Centuries

  Points: 1256

  More actions

  December 5, 2008 at 7:11 am

  #908267

  See here http://www.jlcomp.demon.co.uk/faq/dblink_commit.html and also discussion here http://www.freelists.org/post/oracle-l/Remote-Select-opening-transaction why even distributed queries start transaction.

  Gints Plivna
  http://www.gplivna.eu