Updating a binary field taking a file from filesystem without using dynamic SQL

Question

The Scenario

I have a table (let's call it myTable) in Sql Server 2005 with some columns, including a varbinary(max) field:

• REPORT_ID int (primary key)
• REPORT_FILE varbinary(max)
• ...

I often need to update the files contained inside the varbinary(max) column via TSQL scripts reading a file from the filesystem. These scripts are executed in Sql Server Management Studio.

I wrote the following script to update the binary value using OPENROWSET:

begin try
    begin tran
    update myOtherTable set field1 = 'value1'
    --other instructions
    --...

    UPDATE myTable 
    SET [REPORT_FILE] = (SELECT * FROM OPENROWSET(BULK 'c:\filename.ext', SINGLE_BLOB) AS T)
    WHERE REPORT_ID = ...
end try

begin catch
    if @@TRANCOUNT > 0 begin
        rollback tran
        --other instructions for error handling
        --...
    end
end catch

if @@TRANCOUNT > 0  begin
    commit tran
end 

The Problem

I want to handle the scenario where the file is missing or the filename is wrong; the desired behavior is:

• rollback if an error occurs
• commit if everything worked

If the input file is missing the above script generates an error, but the CATCH block fails to capture the exception, so the transaction and the update to myOtherTable remain pending because the COMMIT instruction isn't executed. As a result I have to rollback the transaction manually.

My solution

The only way I have found to capture the error in the CATCH block is using dynamic TSQL, so my actual script is:

declare @dSQL nvarchar(max)

begin try
    begin tran
        update myOtherTable set field1 = 'value1'
        --other instructions 
        --...
        set @dSQL = '
            UPDATE myTable 
            SET [REPORT_FILE] = (SELECT * FROM OPENROWSET(BULK ''c:\filename.ext'', SINGLE_BLOB) AS T)
            WHERE REPORT_ID = ...
             '
    exec sp_executesql @dSQL
end try

begin catch
    if @@TRANCOUNT > 0 begin
        rollback tran
        --other instructions for error handling
        --...
    end
end catch

if @@TRANCOUNT > 0  begin
    commit tran
end

The Question

Is it possible to achieve the desired behavior without using dynamic TSQL?

Answer 1

It might be best if you just check if the file exists first.

DECLARE @isExists INT;
EXEC master.dbo.xp_fileexist 'C:\filename.ext', @isExists OUTPUT

--If the file exists, then 
IF(@isExists = 1)
BEGIN
    SELECT 'It exists!'
    --Do your work here
END
ELSE
BEGIN
    --Return some kind of error message
    SELECT 'File does not exist.'
END