Handling errors in looped OPENDATASOURCE query

• Andy Cutler

  SSC Journeyman

  Points: 90

  More actions

  October 18, 2005 at 7:31 am

  #91577

  Hi

  I've got a problem with MSDTC errors and using OPENDATASOURCE.

  I have an SP (let's call it SP-ONE) which creates a cursor of remote servers ipaddresses and then loops, passing the ipaddress into another SP (let's call it SP-TWO). SP-TWO has the OPENDATASOURCE query which connects to the remote server and pulls back the data I want into a table.

  Now this all loops fine until there is a problem connecting to a remote servers MSDTC service. I've tried putting errorhandling in SP-TWO so that once it executes the OPENDATASOURCE command, if there is a problem then simply return 0 to SP-ONE.

  I also have error handling in SP-ONE which will continue the loop if there's a problem with the call to SP-TWO

  Unfortunately it does not work and SP-ONE will stop processing if SP-TWO encounters a problem connecting to the remote servers MSDTC service. This isn't really what I want as I need to perform this routine once a day on nearly 200 remote SQL Servers and you just can't tell when there's going to be a problem with one of them!

  Any ideas?

  Thanks

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 18, 2005 at 8:42 am

  #597944

  Yo need to move the connection out of process. There are two possible solutions to this kind of problems.

  1. Implement it with DMO instead of OpendataSource.

  2. Use a job to run OPENDATASOURCE procedure and check for success or failure of the job.

  Hope this helps

  
  * Noel