SQL procedure example for OPENROWSET with TRY CATCH and TRAN

I am posting this example for two reasons. Firstly, it is a great template that I have been using a lot of late so my blog is naturally a good place to store such things. But I also wanted to get this example public because of the time I spent myself looking for a way to catch errors when using the OPENROWSET linked server. As you will see, the answer is in dynamic SQL.

create proc proc_MyProcName (@ReturnMsg varchar(max) output) as begin

     --declare @ReturnMsg varchar(max) --for testing

     declare @dSQL nvarchar(max) -- use dynamic SQL to catch possible errors with linked CSV file
     set @dSQL = '
                  insert into tbl_MyTable
                  select *
                  from OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=D:\DBServerFolder\;HDR=Yes;FORMAT=Delimited(,)'', ''SELECT * FROM [MyImportFile.csv]'')
               -- for excel, use
               -- from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source="D:\DBServerFolder\TheFile.xlsx"; Extended properties=Excel 12.0'' )...Sheet1$
     begin try
          begin tran
          -- my other statements
          -- ... ... ...
          exec sp_executesql @dSQL
     end try

     begin catch
          if @@TRANCOUNT > 0 begin
               rollback tran
               set @ReturnMsg = 'An error ocurred and the transaction was rolled back.' + char(13) + char(13) + 'The SQL error msg is: ' + ERROR_MESSAGE()
               return -1
               --print @ReturnMsg --for testing
     end catch

     if @@TRANCOUNT > 0  begin
          commit tran
          set @ReturnMsg = 'My success message.'
          return 0
          --print @ReturnMsg --for testing


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s