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
Edward JoellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Edward JoellAuthor Commented:
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.
0
Edward JoellAuthor Commented:
I have not forgotten this issue.  I am currently being otherwised tasked.  I will return to this issue when I am done.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Surendra NathTechnology LeadCommented:
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

0
Edward JoellAuthor Commented:
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.
0
Surendra NathTechnology LeadCommented:
errr.. my mistake, between sql and oracle...

Ok, check this out and it should work

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


 COMMIT TRAN


 END TRY
 BEGIN CATCH
  ROLLBACK TRAN
 END CATCH

                                     

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Edward JoellAuthor Commented:
So all you did was pull the "go"s out of the code generated by SQL Server?
0
Surendra NathTechnology LeadCommented:
thats right.
0
Edward JoellAuthor Commented:
Excellent Thanks.
0
Edward JoellAuthor Commented:
Good Answer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.