SSRS - Ignore if Database Doesn't Exist

Question

I have a report created in SSRS which has a number of data sources.

On very rare occasions, one of those data sources may have been detached. This happened recently, as the SSAS Database was temporarily detached on the Dev server to free up memory for a large job.

When that happened, the entire report refused to run, throwing out the error "Query Execution failed for dataset 'DatasetName' (rsErrorExecutingCommand) Either the user, 'UserName', does not have access to the 'DBName' database, or the database does not exist.

Is there anyway to amend either the dataset, or perhaps the query in the datasource, so that if the query fails (because the DB is down / detached) that it still runs everything else, but perhaps shows an error on the report.

[EDIT]

With assistance from Bushell - this is what I ended up using:

1) On the SQL Server (which should always be up) - I created a Linked Server to the SSAS instance

2) Changed my Datasource in SSRS to point to the SQL Server instead of the SSAS instance

3) Used this query (see below) to check whether the SSAS linked server was up - I haven't been able to test with SSAS down, but it does work while it's up! (if anyone reading this is using the same method, you'd just have to replace my 'Select Distinct ... etc.' with your own query)

BEGIN TRY
EXEC sp_testlinkedserver N'SSAS_LinkedServer';

EXEC sp_executesql N'SELECT * FROM OPENQUERY(SSAS_LinkedServer, 
      ''SELECT 
    DISTINCT
        [CATALOG_NAME] as [Database],
        [CUBE_NAME],
        DIMENSION_CAPTION AS [Dimension], 
        DIMENSION_CARDINALITY AS [Count]
    FROM $system.MDSchema_Dimensions
        ORDER BY DIMENSION_CARDINALITY DESC;'');';
END TRY

BEGIN CATCH
SELECT 
        '' as [Database],
        '' as [CUBE_NAME],
        '' AS [Dimension], 
        '' AS [Count]
END CATCH

Thanks to Bushell for pointing me in the right direction.

You could build your dataset in a Stored Procedure, and then use a Try/Catch statement to pull in data from the potentially detached DB. If the try fails, simply return no data, or just the column headers. — bushell, Jul 27 '17 at 16:04

score 2 · Accepted Answer · answered Jul 27 '17 at 16:19

2

I would move your datasets into callable Stored Procedures, and then use TRY/CATCH blocks to determine whether the selects run without errors. And in the instance when there is an error, just return the column headers and no rows.

BEGIN TRY
    SELECT * FROM dbo.DetachedDB
END TRY

BEGIN CATCH
    SELECT '' as [Column1], '' as [Column2]; etc....
END CATCH;

Then in your SSRS report if the count of rows of a dataset are zero, toggle the visibility of the tables, and show an error message which can be set to show instead.

answered Jul 27 '17 at 16:19

bushell

560
3
10

1

Thanks for this Bushell, I may not have time to test this before the weekend, but the logic seems sound to me, so marked as answer - unfortunately don't have enough rep to upvote, but do appreciate your time & help. – Celador Jul 28 '17 at 10:39
Thanks, my example is overly simplified, but the concept will work if correctly implemented in Stored Procedures. I was thinking about it some more last night, and the following link may also provide an alternative solution. https://stackoverflow.com/questions/3104186/sql-server-is-there-an-if-exists-test-for-a-linked-server – bushell Jul 28 '17 at 11:07
Thanks again, I've edited my original question to show what I have eventually used - unfortunately I've not been able to test with SSAS down - but I can't see why it wouldn't work! – Celador Jul 28 '17 at 14:57

SSRS - Ignore if Database Doesn't Exist

1 Answers1