Check Data Source Connectivity

• S. Kusen

  SSChampion

  Points: 10936

  More actions

  December 19, 2014 at 12:17 pm

  #293240

  Does anyone know of a way to gracefully handle if a connection to a data source isn't possible? Stringing off of another post that I made, I'm trying to see if I can put together a dashboard, but if a SQL Server is down or connectivity to that instance isn't possible, I'd like to easily capture that error and simply say "cannot connect to data source".

  I was able to set the timeout to 5 seconds, and that seems to work, but I can't capture that error and return a simple message.

  To give some background, I have a main report and a subreport. The subreport runs a query against a remote system (dynamically providing the instance name from a tablix in the main report), and then the connection string in the subreport connects to the instance name passed.

  I have three instances I'm testing with, and I manually took instance 3 offline for testing this out. So, on the dashboard, I wind up with is this:

  Instance 1 | instance 1 is good

  Instance 2 | instance 2 is good

  Instance 3 | Data retrieval failed for the subreport "instance_health", located at: /instance_health. Please check the log files...

  I'm interested in seeing that if the subreport doesn't load, just report "instance 3 data retrieval failed, needs to be checked"

  Thanks in advance,

  Steve