This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- Re: Sql Server GO breaking script
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 14, 2011
05:56 PM
Sql Server GO breaking script
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...
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...
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.
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.
(4) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 14, 2011
08:06 PM
I tried to stick the the following in a stored procedure to be executed later in the script as a workaround...
However, IS seems to split the script in two on a GO statement even if it's inside a string constant!
Help please.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 15, 2011
10:29 AM
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:
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.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 15, 2011
11:38 AM
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 15, 2011
04:54 PM
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!
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!
![](/skins/images/D60DF682EA37BF37032955CE10F3F0BB/responsive_peak/images/icon_anonymous_message.png)