Sql Server GO breaking script

[zielin]

Hi,

I've tried to search the forum for this issue, but was not having much luck as the search omits the word GO. I have a sql script that executes along with my installer that needs to do the following...

SET IMPLICIT_TRANSACTIONS OFF

GO

-- Set selected recovery model.
ALTER DATABASE $(DatabaseName)
SET RECOVERY $(RecoveryModel)

GO

The $() values are substituted with values based on what the user has chosen in the UI. The problem is that IS seems to break the script up each time it comes across a GO as far as I can tell from profiler. So when I disable implicit transactions it does so in a different session than the alter database statement. This ends up causing an error as an alter database statement can not be run inside of a transaction.

Is this a bug? Is there a workaround for it?

I've also tried the following...

SET IMPLICIT_TRANSACTIONS OFF

--$(GO)

-- Set selected recovery model.
ALTER DATABASE $(DatabaseName)
SET RECOVERY $(RecoveryModel)

GO

and then set up the text replacement to substitute "--$(GO)" with "GO". This results in "Error 27506 Incorrect syntax near 'GO'."

Thanks in advance for your help.

[zielin]

I tried to stick the the following in a stored procedure to be executed later in the script as a workaround...

DECLARE @sql VARCHAR(400)
		
		SET @sql = '
			SET IMPLICIT_TRANSACTIONS OFF
			
			IF @@TranCount > 0
			BEGIN
				COMMIT TRAN
			END
		
			GO

			-- Set selected recovery model.
			ALTER DATABASE ' + @DatabaseName + '
			SET RECOVERY ' + @RecoveryModel + '

			GO'
			
		EXEC sp_executesql @sql

However, IS seems to split the script in two on a GO statement even if it's inside a string constant!

Help please.

[Kramer]

I believe the problem is within your TSQL statement and not InstallShield. sp_executesql strings are not parsed for syntax or execution plans until they are executed so it's just not showing up.

The issue is that you can't have a 'GO' statement within a batch call like that. (http://msdn.microsoft.com/en-us/library/ms175170.aspx)

Try this:

DECLARE @sql VARCHAR(400)
		
		SET @sql = '
			SET IMPLICIT_TRANSACTIONS OFF
			
			IF @@TranCount > 0
			BEGIN
				COMMIT TRAN
			END
		
			-- Set selected recovery model.
			ALTER DATABASE ' + @DatabaseName + '
			SET RECOVERY ' + @RecoveryModel + '
                        '
			
		EXEC sp_executesql @sql

If you HAVE to have a specific sequence of actions with different transactions, you will need to create multiple exec commands and set error validation conditions on each.

[hidenori]

By default, InstallShield uses 'GO' as a batch separator which is interpreted by the run time as a signal that it should send the current batch of SQL statements to a database server. You can specify a different batch seperator in the Batch Separator setting on the Advanced tab for your connection.

[zielin]

Thank you both for the suggestions. I did get this working this morning with something similar to what Kramer suggested. I was able to pull the code back out of the stored procedure and get it to execute fine without the GO. I just had to make sure to commit the implicit transaction that was created before attempt the ALTER statement.

hidenori, I did see the separator string in the project. I tried setting it to something it would not find in the script - DO_NOT_BATCH. Effectively telling it to send the entire script without batching. The result was an error telling me the script was too big and to split it up. However, if I captured the content of the script using profiler and ran it manually through SSMS it worked fine. So I believe this to be an issue with IS.

Further, IS appears to split a script on the batch separator even if it is inside a string constant. I'm pretty sure this is not the intended behavior.

For example, if I have a script that creates a procedure whose contents contain a string constant with the word GO in it (similar to my post below) it will incorrectly split the CREATE PROCEDURE statement into two batches from what I could tell using profiler.

End result is I found a way around it for the time being, but I still believe there to be a bug or two in IS. Thanks again!