SSRS External Doc Link Connection Check

• gbargsley

  SSCommitted

  Points: 1514

  More actions

  February 7, 2011 at 6:07 pm

  #233976

  Hello everyone...

  I have a new issue that was brought to our attention today. I have an SSRS report that runs a stored procedure that collects some data from a database and then runs a call to a web service and builds a link to a word document. Each report will return about 20 rows and is linked to a separate word document.

  The issue that was brought to our attention is that Server A which houses the word documents additionally goes offline which makes the word documents not reachable. So today I built a SQL Agent job that will synchronize Server A word documents to Server B every 2 hours.

  What I would like to add in my stored procedure is to do some kind of check to see if Server A is available and if so return the link to the word document on Server A, if it is not available return the link to Server B word document.

  Not sure if a command shell to ping Server A will work or if there is something else that could accomplish this.

  Any ideas or suggestions would be helpful.

  Thanks

  Garry B