Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Whats wrong with it, Transaction mismatch.. Please help. Expand / Collapse
Author
Message
Posted Sunday, May 12, 2013 7:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:00 AM
Points: 450, Visits: 640
IF OBJECT_ID('test_parent_table') IS NOT NULL
DROP TABLE test_parent_table
CREATE TABLE test_parent_table
( id INT,
name VARCHAR(30)
)
GO

IF OBJECT_ID('DataError') IS NOT NULL
DROP TABLE DataError
CREATE TABLE DataError
( ErrorNo INT,
ErrorDesc VARCHAR(1000),
ErrorProc VARCHAR(100),
ErrorLineNumber INT,
ErrorDateTime DATETIME2(7)
)
GO

IF OBJECT_ID('ThrowError') IS NOT NULL
DROP PROCEDURE ThrowError
GO

CREATE PROCEDURE ThrowError
AS
BEGIN
DECLARE @errorNo INT,
@errorDesc VARCHAR(1000),
@errorProc VARCHAR(100),
@errorLineNumber INT,
@errorDateTime DATETIME2(7)

SELECT @errorNo = ERROR_NUMBER(),
@errorDesc = ERROR_MESSAGE(),
@errorProc = ERROR_PROCEDURE(),
@errorLineNumber = ERROR_LINE(),
@errorDateTime = GETDATE()

DECLARE @id INT = XACT_STATE()
IF( @errorNo < 50000)
BEGIN

IF XACT_STATE() > 0 ROLLBACK

INSERT INTO DataError
SELECT @errorNo,
@errorDesc,
@errorProc,
@errorLineNumber,
@errorDateTime

--RAISERROR(@errorDesc,16,1);
--RETURN 1

END
ELSE
BEGIN
RAISERROR(@errorDesc,16,1);
END
END;
GO

IF OBJECT_ID('test_child') IS NOT NULL
DROP PROCEDURE test_child
GO

CREATE PROCEDURE test_child
( @id INT,
@name VARCHAR(50)
)
AS
BEGIN
BEGIN TRY

INSERT INTO test_parent_table
SELECT @id,
@name

SELECT 1/0

RETURN 0
END TRY
BEGIN CATCH
EXEC ThrowError
RETURN 1
END CATCH
END
GO

IF OBJECT_ID('test_parent') IS NOT NULL
DROP PROCEDURE test_parent
GO
CREATE PROCEDURE test_parent
( @id INT,
@name VARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN

INSERT INTO test_parent_table
VALUES( 1,'Harry')

EXEC test_child 2,'Kristena'

SELECT 1/0

INSERT INTO test_parent_table
VALUES( 3,'Chris')

IF( @@TRANCOUNT > 0)
COMMIT

RETURN 0
END TRY
BEGIN CATCH
EXEC ThrowError
END CATCH
END
GO


/* Test cases

EXEC test_parent 0,'test'

SELECT * FROM test_parent_table
SELECT * FROM DataError



*/

WHEn I run the test case above , the output in DataError is given below:-

ErrorDesc
1 Row :- Divide by zero error encountered.
2 Row :- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Here second row is not supposed to come, this is sucking. Please help!


Regards
Ashok



Post #1451901
Posted Sunday, May 12, 2013 7:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:00 AM
Points: 450, Visits: 640
AND ANSWER IS

IF OBJECT_ID('test_parent_table') IS NOT NULL
DROP TABLE test_parent_table
CREATE TABLE test_parent_table
( id INT,
name VARCHAR(30)
)
GO

IF OBJECT_ID('DataError') IS NOT NULL
DROP TABLE DataError
CREATE TABLE DataError
( ErrorNo INT,
ErrorDesc VARCHAR(1000),
ErrorProc VARCHAR(100),
ErrorLineNumber INT,
ErrorDateTime DATETIME2(7)
)
GO

IF OBJECT_ID('ThrowError') IS NOT NULL
DROP PROCEDURE ThrowError
GO

CREATE PROCEDURE ThrowError
AS
BEGIN
DECLARE @errorNo INT,
@errorDesc VARCHAR(1000),
@errorProc VARCHAR(100),
@errorLineNumber INT,
@errorDateTime DATETIME2(7)

SELECT @errorNo = ERROR_NUMBER(),
@errorDesc = ERROR_MESSAGE(),
@errorProc = ERROR_PROCEDURE(),
@errorLineNumber = ERROR_LINE(),
@errorDateTime = GETDATE()

DECLARE @id INT = XACT_STATE()
IF( @errorNo < 50000)
BEGIN

IF XACT_STATE() <> 0
BEGIN
ROLLBACK
DECLARE @id34 INT = XACT_STATE()
END

INSERT INTO DataError
SELECT @errorNo,
@errorDesc,
@errorProc,
@errorLineNumber,
@errorDateTime

RAISERROR(@errorDesc,16,1);
--RETURN 1

END
ELSE
BEGIN
RAISERROR(@errorDesc,16,1);
END
END;
GO

IF OBJECT_ID('test_child') IS NOT NULL
DROP PROCEDURE test_child
GO

CREATE PROCEDURE test_child
( @id INT,
@name VARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN

INSERT INTO test_parent_table
SELECT @id,
@name

SELECT 1/0

DECLARE @id11 INT = @@TRANCOUNT
IF( @@TRANCOUNT > 0)
COMMIT

RETURN 0
END TRY
BEGIN CATCH
DECLARE @id1 INT = XACT_STATE()
DECLARE @id12 INT = @@TRANCOUNT
IF XACT_STATE() <> 0 ROLLBACK
EXEC ThrowError
END CATCH
END
GO

IF OBJECT_ID('test_parent') IS NOT NULL
DROP PROCEDURE test_parent
GO

/* Test cases

EXEC test_parent 0,'test'

SELECT * FROM test_parent_table
SELECT * FROM DataError



*/

CREATE PROCEDURE test_parent
( @id INT,
@name VARCHAR(50)
)
AS
BEGIN
BEGIN TRY
BEGIN TRAN

INSERT INTO test_parent_table
VALUES( 1,'Harry')

EXEC test_child 2,'Kristena'

SELECT 1/0

INSERT INTO test_parent_table
VALUES( 3,'Chris')

DECLARE @id11 INT = @@TRANCOUNT
IF( @@TRANCOUNT > 0)
COMMIT

RETURN 0
END TRY
BEGIN CATCH
DECLARE @id1 INT = XACT_STATE()
DECLARE @id12 INT = @@TRANCOUNT
IF XACT_STATE() <> 0 ROLLBACK
EXEC ThrowError
END CATCH
END
GO

/* Test cases

EXEC test_parent 0,'test'

SELECT * FROM test_parent_table
SELECT * FROM DataError



*/

It is now perfect : I am sorry to bother you guys..
Post #1451904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse