Edward Joell
asked on
Nesting Trans within Try block produces syntax errors.
I am experiencing some baffling behavior with a generated script in SQL Server.
I inserted a column which requires the table to be dropped and recreated. I automatically generated a script to perform this task on deployment. This script contained a transaction (but no rollback).
It worked but when a modification was made to the script showing an additional new column, the script errored during execution and did not rollback. So I added a try-catch block to the script. Now I get three syntax errors so the script won't run. But when I comment out the Begin and End Try and Catch there are no syntax errors. I do not see what the issue is.
This was the code as generated by SQL Server 2008R2
I don't understand what I am doing wrong here. I've tried adding semi colons after the TRYs and the TRANSs. This made no difference. I thought it might not be permissable to use DDL within a Try-Catch block but MS technet example at this site http://technet.microsoft.com/en-us/library/ms179296(v=SQL.105).aspx shows the the use of DDL with the try catch block.
Does anyone have a clue as to the problem
I inserted a column which requires the table to be dropped and recreated. I automatically generated a script to perform this task on deployment. This script contained a transaction (but no rollback).
It worked but when a modification was made to the script showing an additional new column, the script errored during execution and did not rollback. So I added a try-catch block to the script. Now I get three syntax errors so the script won't run. But when I comment out the Begin and End Try and Catch there are no syntax errors. I do not see what the issue is.
This was the code as generated by SQL Server 2008R2
	BEGIN TRANSACTION
		CREATE TABLE dbo.Tmp_GlobalLogging_ActionLog
			(
			ActionID int NOT NULL IDENTITY (1, 1),
			SubjectCN varchar(100) NOT NULL,
			PageCertSubject varchar(200) NULL,
			LogInDate datetime2(7) NOT NULL,
			PageLocation varchar(1000) NOT NULL,
			Client_IP_Address varchar(50) NULL
			) ON [PRIMARY]
		GO
		ALTER TABLE dbo.Tmp_GlobalLogging_ActionLog SET (LOCK_ESCALATION = TABLE)
		GO
		SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog ON
		GO
		IF EXISTS(SELECT * FROM dbo.GlobalLogging_ActionLog)
			INSERT INTO dbo.Tmp_GlobalLogging_ActionLog (ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address)
				SELECT ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address FROM dbo.GlobalLogging_ActionLog
		GO
		SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog OFF
		GO
		DROP TABLE dbo.GlobalLogging_ActionLog
		GO
		EXECUTE sp_rename N'dbo.Tmp_GlobalLogging_ActionLog', N'GlobalLogging_ActionLog', 'OBJECT'
		GO
		ALTER TABLE dbo.GlobalLogging_ActionLog ADD CONSTRAINT
			PK_saic_reg_Action_Log PRIMARY KEY CLUSTERED
			(
			ActionID
			) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
		GO
		COMMIT TRAN
This is the code as ModifiedBEGIN TRY
	BEGIN TRANSACTION
		CREATE TABLE dbo.Tmp_GlobalLogging_ActionLog
			(
			ActionID int NOT NULL IDENTITY (1, 1),
			SubjectCN varchar(100) NOT NULL,
			PageCertSubject varchar(200) NULL,
			LogInDate datetime2(7) NOT NULL,
			PageLocation varchar(1000) NOT NULL,
			Client_IP_Address varchar(50) NULL,
			GLVersion varchar(10)
			) ON [PRIMARY]
		GO
		ALTER TABLE dbo.Tmp_GlobalLogging_ActionLog SET (LOCK_ESCALATION = TABLE)
		GO
		SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog ON
		GO
		IF EXISTS(SELECT * FROM dbo.GlobalLogging_ActionLog)
			INSERT INTO dbo.Tmp_GlobalLogging_ActionLog (ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address)
				SELECT ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address FROM dbo.GlobalLogging_ActionLog
		GO
		SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog OFF
		GO
		DROP TABLE dbo.GlobalLogging_ActionLog
		GO
		EXECUTE sp_rename N'dbo.Tmp_GlobalLogging_ActionLog', N'GlobalLogging_ActionLog', 'OBJECT'
		GO
		ALTER TABLE dbo.GlobalLogging_ActionLog ADD CONSTRAINT
			PK_saic_reg_Action_Log PRIMARY KEY CLUSTERED
			(
			ActionID
			) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
		GO
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		ROLLBACK TRAN
		GO
	END CATCH
These are the errorsMsg 102, Level 15, State 1, Line 33
Incorrect syntax near 'PRIMARY'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'TRY'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CATCH'.
They refer to the line with primary, the line with "end try", and the line with "End Catch"I don't understand what I am doing wrong here. I've tried adding semi colons after the TRYs and the TRANSs. This made no difference. I thought it might not be permissable to use DDL within a Try-Catch block but MS technet example at this site http://technet.microsoft.com/en-us/library/ms179296(v=SQL.105).aspx shows the the use of DDL with the try catch block.
Does anyone have a clue as to the problem
I don't have a rock-solid answer for you, as it looks correct, but a couple of guesses are
Lose the 'ON PRIMARY' that the first error references
Lose the GO lines.
ASKER
I deleted the go statements and the batch ran or failed. let me explain.
I ran it with Rollback tran in the catch block. I got command successful even though the run did not succeed. So I put a SELECT ERROR_MESSAGE() as ErrorMessage; into the catch block. Now the block correctly failed to run, reporting an error that occurs during statement-level recompilation rather than executing the Rollback. (missing table). I see that such errors are not caught by the Catch block. (So how did I get the command successful message initially?)
Whatever I did so locked up the database that now I can't even use the GUI to refresh the list of tables. Time for another reboot.
I ran it with Rollback tran in the catch block. I got command successful even though the run did not succeed. So I put a SELECT ERROR_MESSAGE() as ErrorMessage; into the catch block. Now the block correctly failed to run, reporting an error that occurs during statement-level recompilation rather than executing the Rollback. (missing table). I see that such errors are not caught by the Catch block. (So how did I get the command successful message initially?)
Whatever I did so locked up the database that now I can't even use the GUI to refresh the list of tables. Time for another reboot.
ASKER
I have not forgotten this issue. I am currently being otherwised tasked. I will return to this issue when I am done.
I have corrected your code, try the below now
BEGIN TRY
CREATE TABLE dbo.Tmp_GlobalLogging_ActionLog
(
ActionID int NOT NULL IDENTITY (1, 1),
SubjectCN varchar(100) NOT NULL,
PageCertSubject varchar(200) NULL,
LogInDate datetime NOT NULL,
PageLocation varchar(1000) NOT NULL,
Client_IP_Address varchar(50) NULL,
GLVersion varchar(10)
) ON [PRIMARY]
ALTER TABLE dbo.Tmp_GlobalLogging_ActionLog SET (LOCK_ESCALATION = TABLE)
BEGIN TRANSACTION
SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog ON
IF EXISTS(SELECT * FROM dbo.GlobalLogging_ActionLog)
INSERT INTO dbo.Tmp_GlobalLogging_ActionLog (ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address)
SELECT ActionID, SubjectCN, LogInDate, PageLocation, Client_IP_Address FROM dbo.GlobalLogging_ActionLog
SET IDENTITY_INSERT dbo.Tmp_GlobalLogging_ActionLog OFF
COMMIT TRAN
DROP TABLE dbo.GlobalLogging_ActionLog
EXECUTE sp_rename N'dbo.Tmp_GlobalLogging_ActionLog', N'GlobalLogging_ActionLog', 'OBJECT'
ALTER TABLE dbo.GlobalLogging_ActionLog ADD CONSTRAINT
PK_saic_reg_Action_Log PRIMARY KEY CLUSTERED
(
ActionID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
ASKER
Are you saying that DDL can not go inside a transaction? If that is the case then what is the point in having it inside the try? In your code, if there is an error on line 27, then you will be trying to roll back a transaction that has already been commited.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So all you did was pull the "go"s out of the code generated by SQL Server?
thats right.
ASKER
Excellent Thanks.
ASKER
Good Answer