I have a problem where a stored procedure is throwing an error because it's trying to run/validate an
OPENROWSET query inside it that it doesn't actually have to execute.
The stored procedure that looks something like this:
IF (@flagA = 1) BEGIN INSERT INTO #tmpData(Column1, Column2, Column3) SELECT A, B, C FROM ( SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Text; HDR=Yes; Database=\\server\tmpFiles', 'SELECT * FROM FileA.txt') ) as T END IF (@flagB = 1) BEGIN INSERT INTO #tmpData(Column1, Column2, Column3) SELECT D, E, F FROM ( SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=\\server\tmpFiles\FileB.xls', 'SELECT * FROM [Sheet$]') ) as T END IF (@flagC = 1) BEGIN INSERT INTO #tmpData(Column1, Column2, Column3) SELECT G, H, I FROM ( SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=\\server\tmpFiles\FileC.xls', 'SELECT * FROM [Sheet1$]') ) as T END
Note that each
OPENROWSET references a different file, in a different format, with different column names.
My problem is that if one of the files doesn't exist, or exists in the wrong format (such as someone uploading a FileB as FileC by mistake), I get an error when the procedure tries to run, even though that
OPENROWSET query is never meant to get executed.
For example, if FileB doesn't exist and you try to run the procedure with
@flagB = 0, it will throw an error saying it can't find the linked server for
Or if a user uploads a
FileB as a
FileC by mistake, then corrects themselves by removing
FileC from the form, it will throw an error saying
Cannot process the object "SELECT * FROM [Sheet1$]" next time the procedure runs because FileC won't have a sheet named
Sheet1, even though
@flagC = 0
How can I prevent SQL from validating the
OPENROWSET queries if they're not the ones getting executed?