Enabling Transactions - SQL Server Forums

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

Author

Topic

smcgrath12
Starting Member

3 Posts

Posted - 2004-04-06 : 09:40:36

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???

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-06 : 15:24:35

Use INSERT INTO #table(values) EXEC xp_cmdshell.

Look at the results and continue.

?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

GunterKDR
Starting Member

5 Posts

Posted - 2004-04-06 : 15:43:36

You could also use the BULK INSERT tsql command,
this doesn't address your bcp out concerns, but it gets you half way there.

Good Morning-
Gunter

They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety. -- Benjamin Franklin

smcgrath12
Starting Member

3 Posts

Posted - 2004-04-08 : 15:28:55

Thanks guys. Appreciate your ideas.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources