October 20, 2011 at 7:04 pm
Hi,
I've got this script:
#DECLARE TIMESTAMP FOR THE FILES
$timestamp = Get-Date -Format yyyy-MM-dd-hh_mm
#SCRIPT
SL SQLSERVER:\SQL\"(local)"\DEFAULT\Databases\"MyDB"\Views
dir | %{$_.Script() | Out-File "C:\Temp\04_Views $timestamp.sql" -Append}
When I am trying to run the output of it, I am getting an error:
Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Because the output looks like this:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[MyView1] ...
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[MyView2] ...
Is there a way to put a "GO" between SET commands and the create command?
Thanks.
October 20, 2011 at 9:21 pm
Found a workaround: if you use "IncludeIfNotExists" it would script the views using dynamic sql:
SL SQLSERVER:\SQL\"(local)"\DEFAULT\Databases\"MyDb"\Views
$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.IncludeIfNotExists = 1
dir | %{$_.Script($so) + 'GO' | Out-File "C:\Temp\04_Views.sql" -Append}
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[MyView]
...
But this is not exactly what I want, because such dynamic SQL scripting style makes it harder to read the script: no keyword highlites in Management Studio.
Any ideas?
March 18, 2019 at 12:42 pm
Here is is 2019 and still not fixed. Leave it to Microsoft to put a bug in their code and not bother to fix it in subsequent releases. I had to use the workaround of "IncludeIfNotExists = true". It's a small thing but it really shoves home the fact that MSFT really doesn't give a shirt sometimes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply