SSRS datasource connections

Question

SSRS datasource connections

var05

Hall of Fame

Points: 3011
More actions
November 23, 2012 at 8:36 am

#270436

HI All,
how to handle datasources fetching data from different databases residing in different system
For eg.. I have to access three systems (database in diff systems)and combine the results in one single ssrs report..
e.g
SSRS report in sys D
Database 1(System A)-> ----- generates some Value X
Database 2(System B)-> ----- generates some Value Y
Database 3(System C)-> ----- generates some Value Z
My SSRS report shows the value X,Y,Z in one single report...
I had created seperated data source each pointing to diff databases and it fetches the appropriate value from it....and the reports work fine...
But say when System C is down,then datasource conn fails for the system C..In such a case the ssrs report throws error... Even though system A and B are up and running, the data source connection fails for the 3rd connection and the entire report throws error.
I wanted to show the values X and Y and Value z can be blank or nothing, if the system C is down..Is there any way to handle the above scenario?? I don wanna the entire report to throw error...is there any possbility to handle?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 1

As an alternative, have you considered combining the data from the three databases in a stored procedure and using just one SSRS dataset?

var05 Hall of Fame Points: 3011 More actions · Answer 2

Thanks 4 ur reply!

No I have not used stored procudure...U mean to say one single stored procedure to combine all data from 3 databases ???COuld you please explain me little on it?

Thanks!

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 3

Yes. I mean one stored procedure that combines data from your three databases. I use stored procedures almost exclusively in my reporting. You will need to use linked servers if the databases are on different instances or three-part naming convention (e.g., Adventureworks.dbo.Sales) if the databases are in the same instance. However, I don't know your reporting requirements. A stored procedure should work if you are joining data from the three databases.

var05 Hall of Fame Points: 3011 More actions · Answer 4

Thanks for ur inputs.... I would look in to it..

And similarly I use a web service as a data source connection in another req.... I have webservices which would fetch me data....and im able to call that web service via ssrs xml data source and it works fine...But the same problem exists here also...

I have more than 4 web services......n each web service fecthes data from diff systems...and if one system is down, the entire report throws error.... Is there way to handle these kind of scenarios?

Thanks for ur help!!!

VA123 SSCarpal Tunnel Points: 4272 More actions · Answer 5

I've read that web services can be called from T-SQL in a stored procedure. Perhaps you can put your error handling there?

var05 Hall of Fame Points: 3011 More actions · Answer 6

I was thinking the same...whether to combine data from various web services into SP and handle the error if the system is down...Im nt sure if this can happen...should give a try..

Thanks 4ur help!