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