Preserve some data during Rollback

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)

Preserve some data during Rollback

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-02-04 : 12:01:01

re: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61208

I'd still like a #INCLUDE statement though ....

The CREATE TABLE for the returned results from the Sproc would be in several places. A #INCLUDE for that would be nice.

And for all the standard preamble at the top of the Sproc.

AND I'd like to find a way to standardise COMMIT / ROLLBACK - in particular I would like to be able to preserve logging data already created (and about to be rolled back) by storing it in an @TableVar, doing the ROLLBACk and then re-inserting it - but I've tried to have a child SProc do that, and the ROLLBACK, but then I get a transaction count inconsistency error when I return to the parent SProc

.

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 12:14:35

You can log things within a transaction by writing them on another connection or exporting them to a file.
Neither very nice and you have to deal with the data item by item so it's not suitable for large amounts.

As to the #Include - if needed I'll generate the SP scripts with a processor that adds the code needed.
That only works if you always write and maintain SPs from script files - but if you do then osql is an easy way to apply the scripts after preprocessing.

But you're write - they would both be useful.
I would add having a bulk export command to like bulk insert to allow you to bcp data out within a transaction.
OK the file would still be there on a rollback but you would be able to run things within a transaction for testing without it hanging. It could also be used for the logging bit.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-04 : 12:53:16

I suppose I ought really to "template" the bits that are common to our Sprocs so that they can be freshened up en-masse.

"You can log things within a transaction ..."

Here's what I am trying to do with preserving the logging data after a rollback. It works fine, but I cannot move it to a Child Sproc because I would need to put the ROLLBACK in that Sproc too, and doing so mucks up the warning messages about transaction counts


SET NOCOUNT ON
GO

-- Store "Application" data
CREATE TABLE dbo.MyData
(
	MyRowID		int NOT NULL,
	MyRowData	varchar(50) NOT NULL,
	PRIMARY KEY
	(
		MyRowID
	)
)
GO

-- Store "Logging" data
CREATE TABLE dbo.MyLogTable
(
	MyID		int identity NOT NULL,
	MySessionID	int NOT NULL,
	MyMarker	varchar(50) NOT NULL,
	MyMessage	varchar(1000) NOT NULL,
	MyErrorCode	int NOT NULL,
	PRIMARY KEY
	(
		MyID
	)
)
GO

CREATE PROCEDURE dbo.MySProc
AS
-- Some ID representing the particular logging session to be "preserved"
DECLARE	@ThisSessionID	int
SELECT	@ThisSessionID = 1234	-- A dummy value for testing purposes

DECLARE	@intMyErrorNo	int	-- Local error number
SELECT	@intMyErrorNo = 0	-- Default to "No Error"

-- ... statements, including logging stuff as necessary ...

	-- Log event (A)
	INSERT INTO dbo.MyLogTable(MySessionID, MyMarker, MyMessage, MyErrorCode)
	SELECT	@ThisSessionID, '(A)', 'At point 1', 0

	-- Insert application data (OUTSIDE the TRANSACTION)
	INSERT INTO dbo.MyData(MyRowID, MyRowData)
	SELECT	1, 'AAAA'

	-- Start section requiring TRANSACTION block
	BEGIN TRANSACTION MyLabel_01
	SAVE  TRANSACTION MyLabel_02

-- ... stuff ...

	-- Log event (B)
	INSERT INTO dbo.MyLogTable(MySessionID, MyMarker, MyMessage, MyErrorCode)
	SELECT	@ThisSessionID, '(B)', 'At point 2', 0

	-- This data will be rolled back
	-- Insert application data (INSIDE the TRANSACTION)
	INSERT INTO dbo.MyData(MyRowID, MyRowData)
	SELECT	2, 'BBBB'

-- Simulate some sort of error
	SELECT @intMyErrorNo = 1
	IF @intMyErrorNo <> 0 GOTO MyExitLabel	-- ABort!

MyExitLabel:
	IF @intMyErrorNo = 0
	BEGIN
		COMMIT TRANSACTION MyLabel_01
	END
	ELSE
	BEGIN
		-- Preserve logging data in @TempTable
DECLARE @TempTable TABLE
(
	T_MyID		int NOT NULL,	-- Stored IDENTITY from dbo.MyLogTable
	T_MySessionID	int NOT NULL,
	T_MyMarker	varchar(50) NOT NULL,
	T_MyMessage	varchar(1000) NOT NULL,
	T_MyErrorCode	int NOT NULL,
	PRIMARY KEY
	(
		T_MyID
	)
)

		-- Store all Logging Data for this session
		INSERT INTO @TempTable
		SELECT	*
		FROM	dbo.MyLogTable
		WHERE	MySessionID = @ThisSessionID

		ROLLBACK TRANSACTION MyLabel_02
		COMMIT TRANSACTION MyLabel_01

		-- Reinstate logging data [only rolled back rows]
		INSERT INTO dbo.MyLogTable
		(
			MySessionID, MyMarker, MyMessage, MyErrorCode
		)
		SELECT	T_MySessionID, T_MyMarker, T_MyMessage, T_MyErrorCode
		FROM	@TempTable T
			LEFT OUTER JOIN dbo.MyLogTable L
				ON L.MyID = T.T_MyID
		WHERE	L.MyID IS NULL	-- Put back if not exists
	
	END

	-- Display Application data (Should be only 1 row of 2)
	SELECT	*
	FROM	dbo.MyData

	-- Display Logging data (Should be 2 rows of 2, but with a gap in ID numbers)
	SELECT	*
	FROM	dbo.MyLogTable

	RETURN @intMyErrorNo
GO

EXEC dbo.MySProc
GO

DROP PROCEDURE dbo.MySProc
GO

DROP TABLE dbo.MyData
GO
DROP TABLE dbo.MyLogTable
GO
SET NOCOUNT OFF
GO

Kristen

Kristen
Test

22859 Posts

Posted - 2006-02-04 : 13:18:20

Here's the code to (try!) to use a Child Sproc for the ROLLBACK


SET NOCOUNT ON
GO

-- Store "Application" data
CREATE TABLE dbo.MyData
(
	MyRowID		int NOT NULL,
	MyRowData	varchar(50) NOT NULL,
	PRIMARY KEY
	(
		MyRowID
	)
)
GO

-- Store "Logging" data
CREATE TABLE dbo.MyLogTable
(
	MyID		int identity NOT NULL,
	MySessionID	int NOT NULL,
	MyMarker	varchar(50) NOT NULL,
	MyMessage	varchar(1000) NOT NULL,
	MyErrorCode	int NOT NULL,
	PRIMARY KEY
	(
		MyID
	)
)
GO

CREATE PROCEDURE dbo.MySProc2R
	@ThisSessionID	int,
	@strLabel1	varchar(1000),	-- Rollback placeholder names
	@strLabel2	varchar(1000)
AS
	
-- Preserve logging data in @TempTable
DECLARE @TempTable TABLE
(
	T_MyID		int NOT NULL,	-- Stored IDENTITY from dbo.MyLogTable
	T_MySessionID	int NOT NULL,
	T_MyMarker	varchar(50) NOT NULL,
	T_MyMessage	varchar(1000) NOT NULL,
	T_MyErrorCode	int NOT NULL,
	PRIMARY KEY
	(
		T_MyID
	)
)

	-- Store all Logging Data for this session
	INSERT INTO @TempTable
	SELECT	*
	FROM	dbo.MyLogTable
	WHERE	MySessionID = @ThisSessionID

	ROLLBACK TRANSACTION @strLabel2
	COMMIT TRANSACTION @strLabel1

	-- Reinstate logging data [only rolled back rows]
	INSERT INTO dbo.MyLogTable
	(
		MySessionID, MyMarker, MyMessage, MyErrorCode
	)
	SELECT	T_MySessionID, T_MyMarker, T_MyMessage, T_MyErrorCode
	FROM	@TempTable T
		LEFT OUTER JOIN dbo.MyLogTable L
			ON L.MyID = T.T_MyID
	WHERE	L.MyID IS NULL	-- Put back if not exists

GO

CREATE PROCEDURE dbo.MySProc2
-- This Sproc uses a child Sproc for the rollback
AS
-- Some ID representing the particular logging session to be "preserved"
DECLARE	@ThisSessionID	int	
SELECT	@ThisSessionID = 1235	-- A dummy value for testing purposes

DECLARE	@intMyErrorNo	int	-- Local error number
SELECT	@intMyErrorNo = 0	-- Default to "No Error"

-- ... statements, including logging stuff as necessary ...

	-- Log event (A)
	INSERT INTO dbo.MyLogTable(MySessionID, MyMarker, MyMessage, MyErrorCode)
	SELECT	@ThisSessionID, '(A)', 'At point 1', 0

	-- Insert application data (OUTSIDE the TRANSACTION)
	INSERT INTO dbo.MyData(MyRowID, MyRowData)
	SELECT	3, 'CCCC'

	-- Start section requiring TRANSACTION block
	BEGIN TRANSACTION MyLabel_01
	SAVE  TRANSACTION MyLabel_02

-- ... stuff ...

	-- Log event (B)
	INSERT INTO dbo.MyLogTable(MySessionID, MyMarker, MyMessage, MyErrorCode)
	SELECT	@ThisSessionID, '(B)', 'At point 2', 0

	-- This data will be rolled back
	-- Insert application data (INSIDE the TRANSACTION)
	INSERT INTO dbo.MyData(MyRowID, MyRowData)
	SELECT	4, 'DDDD'

-- Simulate some sort of error
	SELECT @intMyErrorNo = 1
	IF @intMyErrorNo <> 0 GOTO MyExitLabel	-- ABort!

MyExitLabel:
	IF @intMyErrorNo = 0
	BEGIN
		COMMIT TRANSACTION MyLabel_01
	END
	ELSE
	BEGIN

		EXEC dbo.MySProc2R
			@ThisSessionID	= @ThisSessionID,
			@strLabel1	= 'MyLabel_01',
			@strLabel2	= 'MyLabel_02'
	END

	-- Display Application data (Should be only 1 row of 2)
	SELECT	*
	FROM	dbo.MyData

	-- Display Logging data (Should be 2 rows of 2, but with a gap in ID numbers)
	SELECT	*
	FROM	dbo.MyLogTable

	RETURN @intMyErrorNo
GO

EXEC dbo.MySProc2
GO

DROP PROCEDURE dbo.MySProc2
GO
DROP PROCEDURE dbo.MySProc2R
GO
-------------------------------

DROP TABLE dbo.MyData
GO
DROP TABLE dbo.MyLogTable
GO
SET NOCOUNT OFF
GO

which gives the message:

Server: Msg 266, Level 16, State 2, Procedure MySProc2R, Line 33
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 1.

Kristen

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 08:42:13

<bump>!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources