0

I know that the solution is add a 'GO' statement at the begin of the code but the fact is, the final objective is a PowerShell script in order to automate the creation of all objects of a database and then start a new database creation in other server with other different version and then migrate all the data to the new server and DB.

The problem is when I use the command (Export-DbaScript) to export the SMO script creation and when I use the command 'Invoke-DbaQuery' in order to create for example a stored procedure or view then failed with this message:

CREATE VIEW must be the only statement in the batch

Here part of my code:

$Options = New-DbaScriptingOption
$Options.AppendToFile= $False
$Options.AllowSystemObjects= $False
$Options.ClusteredIndexes= $True
$Options.DriAll= $True
$Options.ScriptDrops= $False
$Options.IncludeHeaders=$False
$Options.ToFileOnly=$True
$Options.Indexes = $True
$Options.Permissions=$True
$Options.WithDependencies=$False

Get-DbaDbView -SqlInstance Myserver -Database MyDatabase -View MyView | Export-DbaScript -ScriptingOptionsObject $Options  -Path  MyPath.SQL

Invoke-DbaQuery -SqlInstance Myserver -Database MyDatabase -File MyPath.SQL

The problem is with the two first statements (SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON)

Is any option with the command 'Export-DbaScript' to avoid those commands or at least add 'GO'statement right after the command?

I prefer avoid manual fix due to one of the objectives is a dynamically script with very few manual contact from IT

Example output

SET ANSI_NULLS ON         -- see the note below 
SET QUOTED_IDENTIFIER ON 

CREATE VIEW [dbo].[viewtest]
AS
SELECT * FROM [dbo].[tabletest]

Note: Putting the 'GO' statement after the SET commands above solves the problem but it is a manual fix, I can not do that in the hundreds of automated scripts I generate.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SakZepelin
  • 1
  • 1
  • Set [`$Options.BatchSize`](https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions.batchsize?view=sql-smo-160#microsoft-sqlserver-management-smo-scriptingoptions-batchsize)? – JosefZ Feb 14 '23 at 14:26
  • 1
    I think you're looking for the `ScriptBatchTerminator` property on your ScriptingOptions object. On my machine, it defaults to false; try setting it to true and see if that fixes your issue. – Ben Thul Feb 14 '23 at 14:34
  • @BenThul I was able to add the 'GO' statement in all script, the bad news are that option is creating a lot of empty spaces between letters. example ``` S E T A N S I _ N U L L S O N G O S E T Q U O T E D _ I D E N T I F I E R O N G O C R E A T E V I E W [ d b o ] . [ v i e w t e s t ] A S S E L E C T * F R O M [ d b o ] . [ t a b l e t e s t ] ``` – SakZepelin Feb 14 '23 at 16:04
  • What editor did you use to open the resulting file? I as because I've seen that behavior from notepad not correctly handling the file being UTF-16 encoded. – Ben Thul Feb 15 '23 at 15:52

0 Answers0