Enabling transactions having BCP operations.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Enabling transactions having BCP operations.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-19 : 10:55:41

Steve writes "SET XACT_ABORT ON

BEGIN TRAN

Declare @mysql nvarchar(3000)

Set @mysql = "insert into..."
Exec sp_executesql @mysql
IF (@@ERROR <> 0)
BEGIN
GOTO tran_failure_block
END

Set @mysql = "delete from..."
Exec sp_executesql @mysql
IF (@@ERROR <> 0)
BEGIN
GOTO tran_failure_block
END

Set @sql = 'Exec master..xp_cmdshell (Runs a BCP In/Out here)'
Exec (@sql)
IF (@@ERROR <> 0)
BEGIN
GOTO tran_failure_block
END

COMMIT TRAN
RETURN

tran_failure_block:
BEGIN

ROLLBACK TRAN
RETURN
END

GO

This is the meat of my stored procedure. Anyways, the problem is with
the last command (xp_cmdshell) which I use to run BCP in and out operations.
Now, the problem is, if there is any error in the BCP, I can't catch it in
the stored proc, as I would imagine, BCP runs out of process from sql server.
I really want everything to go, or nothing to go and if there any errors in my
xp_cmdshell bcp, I want to catch it and rollback the entire transaction.

Any ideas???"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources