Dear Jonathan,

Can I run this SP (a bit changed to create XML file and return 0 if file is exorted w/o errors or 1 if not) in my transaction to check if the file was successfully exported and update Export flag if yes? The code would look like this:

BEGIN TRAN

some sql selects & inserts preparing the data to export and placing in @XML variable

DECLARE @ID INT

INSERT TrackingTable (XML, Export) VALUES (@XML,0)

SELECT @ID = SCOPE_IDENTITY()

DECLARE @CMD VARCHAR(50), @Result BIT

SET @CMD = 'SELECT XML FROM TrackingTable WHERE ID = ' + CAST(@ID AS VARCHAR)

EXECUTE @Result = [dbo].[WriteResultsToCsvFile] @CMD , 'SomeFileName.xml'

IF(@Result = 1)

BEGIN

ROLLBACK TRAN

RETURN 1 -- error code

END

-- file is exported so update the status

UPDTE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)

COMMIT TRAN

Are there any disadvantages of this approach?

Thanks in advance,

Marek