Link to home
Start Free TrialLog in
Avatar of Edward Joell
Edward JoellFlag for United States of America

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

Open in new window

This is the code as Modified
BEGIN 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

Open in new window

These are the errors
Msg 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'.

Open in new window

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
Avatar of Edward Joell

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

Open in new window

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
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So all you did was pull the "go"s out of the code generated by SQL Server?
thats right.
Excellent Thanks.
Good Answer