Sometimes you definitely need to go with quick workarounds. I am pretty much sure I am not the only BI developer working from time to time with legacy and somehow wacky old code used for production purposes. This time I came across a legacy scheduled stored procedure filling a dataset for SSRS reporting purposes calling openrowset to run MDX query against an OLAP cube but the linked server was failing from time to time because of the weak connections. Whenever the linked server call would fail, there would be simply no reporting as the MDX results were later used in an INNER JOIN 🙂 . I kind of wonder what did the people writing this code thought back then. Anyway I needed this SP to stop failing and to have results even if the linked server connection would fail and to be informed that the linked server call failed so I could react and persist the results from the last successful run.
The solution is quite easy and so far seems bullet proof. Lets use this sample MDX code enveloped in an openrowset for example:
SELECT a.* FROM OpenRowset( 'MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;', 'SELECT Measures.members ON ROWS, [Product Category].members ON COLUMNS FROM [Sales]') as a GO
So the trick is to add this chunk of code after sp_testlinkedserver which tests if we are able to connect to the specified linked server and we need to run this together in the try block. Also we might want to set the variable @err to know that an error happened. The code could look something like this:
DECLARE @err BIT = 0; BEGIN TRY EXEC sp_testlinkedserver N'MSOLAP'; SELECT a.* INTO #results FROM OpenRowset( 'MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;', 'SELECT Measures.members ON ROWS, [Product Category].members ON COLUMNS FROM [Sales]' ) as a; END TRY BEGIN CATCH SET @err = 1; END CATCH IF @err = 1 BEGIN --FOR EXAMPLE USE AND PERSIST RESULTS FROM THE LAST TIME THE SP WAS SUCCESSFULLY EXECUTED.. END
Disclaimer: This is just a quick fix tutorial, I definitely agree this is not the best example of using
the try…catch block. Details on sp_testlinkedserver can be easily found here.