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

SQL Server Development (2000)

TRANSACTION

Author

Topic

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-09 : 04:21:57

Hello everyone,

I have the following stored procedure;

DECLARE @input varchar(2000)
SET @input = '<root><personal person="a7"/></root>'
DECLARE @hDoc int

BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc output, @input

INSERT INTO [Te]
SELECT
Number
from OPENXML (@hDoc,'root/personal',3)
with
(
[Number] char(10) '@person'
)

IF @@ERROR <> 0 GOTO ERROR

INSERT INTO [Temp_Table]
SELECT
person
from OPENXML (@hDoc,'root/personal',3)
with
(
[person] char(10) '@person'
)

IF @@ERROR <> 0 GOTO ERROR

EXEC sp_xml_removedocument @hDoc

COMMIT TRAN

ERROR:
IF @@ERROR <> 0
ROLLBACK TRAN

There are two different INSERT statements, which must be commit/rollback separately. The problem with the above SQL, is that the Table 'Te' does not respond (stuck) until I run 'ROLLBACK TRAN' separately in case of Error.

What is the best way to write a Commit and Rollback in the SQL Statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:35:08

quote:
Originally posted by AProgrammer

Hello everyone,

I have the following stored procedure;

DECLARE @input varchar(2000),@ErrorNo int
SET @input = '<root><personal person="a7"/></root>'
DECLARE @hDoc int

BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc output, @input

INSERT INTO [Te]
SELECT
Number
from OPENXML (@hDoc,'root/personal',3)
with
(
[Number] char(10) '@person'
)

SET @ErrorNo = @@ERROR

IF @ErrorNo <> 0
GOTO ERROR

INSERT INTO [Temp_Table]
SELECT
person
from OPENXML (@hDoc,'root/personal',3)
with
(
[person] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 GOTO ERROR

EXEC sp_xml_removedocument @hDoc

COMMIT TRAN

ERROR:
IF @ErrorNo <> 0
ROLLBACK TRAN

There are two different INSERT statements, which must be commit/rollback separately. The problem with the above SQL, is that the Table 'Te' does not respond (stuck) until I run 'ROLLBACK TRAN' separately in case of Error.

What is the best way to write a Commit and Rollback in the SQL Statement?

Note sure @@ERROR will have the value after the program execution reaches the ERROR: block

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-09 : 04:51:04

It does not work either.

DECLARE @ErrorNo INT
DECLARE @input varchar(2000)
SET @input = '<root><personal person="a9"/></root>'
DECLARE @hDoc int

BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc output, @input

INSERT INTO [Te]
SELECT
Number
from OPENXML (@hDoc,'root/personal',3)
with
(
[Number] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0
GOTO ERROR

INSERT INTO [Temp_Table]
SELECT
person
from OPENXML (@hDoc,'root/personal',3)
with
(
[person] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 GOTO ERROR

SET @ErrorNo = @@ERROR
IF @ErrorNo = 0 GOTO NOERROR

EXEC sp_xml_removedocument @hDoc

--COMMIT TRAN

ERROR:
IF @ErrorNo <> 0
ROLLBACK TRAN

NOERROR:
IF @ErrorNo = 0
COMMIT TRAN

If both statements are correct (successful) both tables 'Te' and 'Temp_Table' are stuck until I don't run COMMIT TRAN explicitly.

What is happening?????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:57:33

quote:
Originally posted by AProgrammer

It does not work either.

DECLARE @ErrorNo INT
DECLARE @input varchar(2000)
SET @input = '<root><personal person="a9"/></root>'
DECLARE @hDoc int

BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc output, @input

INSERT INTO [Te]
SELECT
Number
from OPENXML (@hDoc,'root/personal',3)
with
(
[Number] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0
GOTO ERROR

INSERT INTO [Temp_Table]
SELECT
person
from OPENXML (@hDoc,'root/personal',3)
with
(
[person] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 GOTO ERROR

SET @ErrorNo = @@ERROR
IF @ErrorNo = 0 GOTO NOERROR

EXEC sp_xml_removedocument @hDoc

--COMMIT TRAN

ERROR:
IF @ErrorNo <> 0
ROLLBACK TRAN

NOERROR:
IF @ErrorNo = 0
COMMIT TRAN

If both statements are correct (successful) both tables 'Te' and 'Temp_Table' are stuck until I don't run COMMIT TRAN explicitly.

What is happening?????

Thats obvious since you've started a transaction using BEGIN TRAN it has to have either a ROLLBACK TRAN or COMMIT TRAN to indicate either successful or unseccuessful end of transaction. SO it will wait until you end it explicitly by means of COMMIT/ROLLBACK

AProgrammer
Starting Member

9 Posts

Posted - 2008-06-09 : 05:16:25

Hi visakh,

Where I need to do that? Here is my stored procedure now...

DECLARE @ErrorNo INT
DECLARE @input varchar(2000)
SET @input = '<root><personal person="a9"/></root>'
DECLARE @hDoc int

BEGIN TRAN
EXEC sp_xml_preparedocument @hDoc output, @input

INSERT INTO [Te]
SELECT
Number
from OPENXML (@hDoc,'root/personal',3)
with
(
[Number] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0
GOTO ERROR

INSERT INTO [Temp_Table]
SELECT
person
from OPENXML (@hDoc,'root/personal',3)
with
(
[person] char(10) '@person'
)

SET @ErrorNo = @@ERROR
IF @ErrorNo <> 0 GOTO ERROR

COMMIT TRAN

EXEC sp_xml_removedocument @hDoc

ERROR:
IF @ErrorNo <> 0
ROLLBACK TRAN

Even then, the tables are stuck... Why is that???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources