Commit Transaction Even on Trigger Failure

[bwestover]

I am trying to pull data out of an application database and transform it to another medium.
I have direct access to the database, but I cannot alter the program code.

What I want to have happen is that when an insert occurs on a particular table, execute a statement that will put the data it needs into a string and export the file.

I have the code working with one small problem. If something goes wrong in my code (like a conversion error, or the file server is not available), it actually does a ROLLBACK TRANSACTION on the initial insert statement. This is bad because it causes a missing reference in the application database.

What I'd like to have happen is that even if my trigger fails, go ahead and insert the record.

Here's what I have so far:

CREATE trigger [trg_CIM_WO_Issue]
on [dbo].[RunIDMapReportID]
after insert
as
--delay for 1 second to ensure all ingredients finish inserting
waitfor delay '0:0:1.000'

Declare @WO char(4), 
@ComponentItemCode varchar(7), 
@SubWeight decimal(15,5), 
@Lot varchar(25), 
@cimOut nvarchar(4000),
@cnt int

--zero out counter
Set @cnt = 0

--add beginning to work order issue cim file
Set @cimOut = '@@batchload wowois.p'


--Scroll through all the ingredients for the inserted ReportID and add them to the CIM output
Declare cur_0 scroll cursor for
Select C.RunID, B.ComponentItemCode, B.SubWeight, B.LotNumber
From BatchReport A
inner join BatchReportDetail B
on A.ReportID = B.ReportID
inner join RunIDMapReportID C
on A.ReportID = C.ReportID
Where A.BatchDone = 1 and B.ComponentItemCode like '2%' and B.Deduct = 1
and B.ReportID = (Select ReportID From inserted)
Order by B.ComponentItemCode

open cur_0

FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot

While @@Fetch_Status = 0
Begin
Set @cnt = @cnt +1

--first time through add the work order
If @cnt = 1
	Begin
	Set @cimOut = @cimOut + '
' + @WO
	End
--each time add the ingredient issue lines
Set @cimOut = @cimOut + '
' + @ComponentItemCode + '
' + convert(varchar(25),@SubWeight) + ' - - - - ' + @Lot


FETCH NEXT FROM cur_0 into @WO, @ComponentItemCode, @SubWeight, @Lot
End

close cur_0
deallocate cur_0

--Add end line to string
Set @cimOut = @cimOut + '
@@end'

--If we had any data to output, use OLE automation to put it into a timestamped file on the 
--server and write out a history file with the WorkOrder, ReportID and the date it was processed
If @cnt > 0
Begin
	--declare variables necessary for processing the CIM output into a file using OLE
	DECLARE @FS int, @OLEResult int, @FileID int, @FileName varchar(255)

	--Generate a filename with a fixed length timestamp
    Set @FileName = '\\servername\d$\CIM\wois_cim_' + convert(char(8),GETDATE(),112)+replace(convert(char(12),getdate(),114),':','') + '.cim'

	EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
	IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

	--Open the file
	execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
	IF @OLEResult <> 0 PRINT 'OpenTextFile'

	--Write the Text
	execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @cimOut
	IF @OLEResult <> 0 PRINT 'WriteLine'

	--cleanup OLE objects
	EXECUTE @OLEResult = sp_OADestroy @FileID
	EXECUTE @OLEResult = sp_OADestroy @FS

	--write to history log
	Insert tblCIMhist
	Select RunID, ReportID, getdate()
	From inserted
End

[iburyak]

Not sure if it will help but you can try to catch an error:

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
If @@Error <> 0
    PRINT ‘Error Scripting.FileSystemObject'
ELSE
    IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

Good Luck.