How to get status of sp_executesql

Hello

The basic requirement is to import data from excel sheet and save it to a table. The excel filename and sheet name is passed in as a parameter to a stored procedure. I am using sp_executesql to execute the statement.

First I delete all the records from the table and then copy the data from excel into the table. I want to do this as a transaction so that if there is any error in the import process, I want to rollback the transaction otherwise commit it. sp_executesql returns 0 for success or 1 for failure.

In my case, even if there is any syntax error, I get the status of 0. Also the @.@.ROWCOUNT contains some positive number greater than 0 even if some error occurs.

BEGIN TRANSACTION T1

GO

DELETE FROM SORTGROUP

GO

DECLARE @.Statement as NVARCHAR(1024)

DECLARE @.TableName as VARCHAR(32)

DECLARE @.ExcelFile AS VARCHAR(512)

DECLARE @.ParamNameList as VARCHAR(1024)

DECLARE @.SheetName as VARCHAR(64)

DECLARE @.Status as INT

DECLARE @.ErrorCode as INT

DECLARE @.RowCount AS INT

SET @.TableName = 'SORTGROUP'

SET @.ExcelFile = 'E:\PROJECTS\RALEIGH\TEST.XLS'

SET @.ParamNameList = '*'

SET @.SheetName = 'SORTGROUPDATA'

EXEC master..xp_sprintf @.Statement OUTPUT, 'INSERT %s SELECT %s FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE=%s'', ''SELECT %s FROM [%s$]'')', @.TableName, @.ParamNameList, @.ExcelFile, @.ParamNameList, @.SheetName

SET @.Status = -1

exec @.Status=sp_executesql @.Statement

SELECT @.ErrorCode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT

IF @.Status = -1

ROLLBACK TRANSACTION T1

ELSE COMMIT TRANSACTION T1

PRINT @.ErrorCode

PRINT @.RowCount

PRINT @.Status

Is there any reason why you're NOT using DTS to do this? The work flow management will handle all of that for you