Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 FileB

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

There are a couple of options available. One of them is to use dynamic SQL for your insert calls. That way, validation doesn't actually occur until the execution occurs. For your example, it could look like:

declare @sql nvarchar(max);

IF (@flagA = 1)
BEGIN
set @sql = N'
    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
set @sql = N'
    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
set @sql = N'
    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

execute sp_executesql @sql;

In this case, validation wouldn't occur until runtime. The downside is that troubleshooting gets harder because you're now trying to debug dynamic SQL.

You might also be able to merge the several separate statements into one.

A second alternative is to split the call out into separate procedures. I would put this at a distant second-place, mostly because it's easy to abuse this and start pretending that T-SQL stored procedures are equivalent to C-style methods, forgetting that there can be significant performance differences. Anyhow, move the INSERT statements into three separate procedures and pass in any necessary parameters, like so:

IF (@flagA = 1)
BEGIN
    exec InsertFlagA @parms1;
END

IF (@flagB = 1)
BEGIN
    exec InsertFlagB @parms2;
END

IF (@flagC = 1)
BEGIN
    exec InsertFlagB @parms3;
END

You couldn't do this with a temp table, so that might throw this option out unless you had a permanent "temporary" table available (or used a global temp table).

If you did want to use a temp table, you could change the exec statement to insert into #tmpData exec InsertFlagA @parms1.

share|improve this answer
    
Ugh I was hoping SQL provided a way to disable this validation so I didn't have to use dynamic SQL or multiple procedures + global tables. Thank you though, I'll accept your answer if nobody can provide a better one :) I suppose as a 3rd option I could also have my application copy a template version of each file when the file isn't specified to ensure it exists and is in the correct format... –  Rachel Dec 6 '12 at 16:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.