CREATE VIEW must be the only statement in the batch / Export-DbaScript - DBATools

60 Views Asked by At

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.

0

There are 0 best solutions below