July 21, 2016 at 5:23 am
I have an existing SSRS report that pulls data from various tables in a SQL 2008 database but I've been asked to link in some data from an Excel spreadsheet that is updated by users. I think we can all agree that anyone using a spreadsheet for STORING data rather than manipulating and presenting it deserves a good kicking but I'm stuck with the requirement.
I have a working linked server object that allows me to talk to the spreadsheet from T-SQL but I wanted to make this more robust given the possibility that a network user might have the spreadsheet open when someone else runs the report. Hence at the point in my report code where I needed to drag some data out of the spreadsheet into a temporary table, I tried the following:
DECLARE@DCNDataExtracted bit = 0;
CREATE TABLE #DCNEXTRACT (
DCN nvarchar(20),
DrawingNumber nvarchar(100)
);
BEGIN TRY
INSERT INTO #DCNEXTRACT (
DCN,
DrawingNumber
)
SELECT
DCN,
DrawingNumber
FROM OPENQUERY(DCN_SPREADSHEET, 'SELECT [CN Number] AS DCN, [Drawing/Doc No#] AS DrawingNumber FROM [DCN LOG$] WHERE [Completed] IS NULL');
SET @DCNDataExtracted = 1;
END TRY
BEGIN CATCH
SET @DCNDataExtracted = 0;
END CATCH
I was hoping that in the event that the spreadsheet was opened by another user, I could interrogate the value of @DCNDataExtracted and display a report missing the outside data but including a message explaining why. In fact if the spreadsheet is locked by another user, the CATCH clause doesn't run and the SQL falls over with the message:
Msg 7303, Level 16, State 1, Line 9
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DCN_SPREADSHEET".
Does anyone have any idea how I can implement a solution that handles the locked spreadsheet situation in a user friendly way?
Robert
July 21, 2016 at 6:59 am
Please check the following Linked server error not caught by TRY-CATCH
July 21, 2016 at 7:29 am
Thanks for the pointer. Yes ONE of the solutions suggested on that page worked. Note that the suggestion to call:
EXEC sp_testlinkedserver N'DCN_SPREADSHEET';
doesn't work. That jumps the CATCH clause in exactly the same way. The method that works is to put the SQL statement likely to cause the error in a variable and hand the variable to sp_executesql. sp_executesql returns the same message about the locked file but it appears as printed output that can be ignored by the caller and the CATCH clause DOES run.
BOL does say that TRY/CATCH doesn't do you any good if the statement that fails, causes the data connection to close which I assume is why I had the problem but at least I now have a workaround.
Thanks again.
Robert
July 21, 2016 at 7:34 am
Thanks for you feedback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply