Powershell works differently in SQL Server 2014

• Roust_m

  SSCoach

  Points: 17408

  More actions

  October 14, 2014 at 9:39 pm

  #297504

  Hi,

  I had a job to script my database on the old SQL 2008 R2 server:

  #DECLARE TIMESTAMP FOR THE FILES

  $timestamp = Get-Date -Format yyyy-MM-dd

  #SCRIPT

  SL SQLSERVER:\SQL\"MyServer\DEFAULT"\Databases\"MyDB"\StoredProcedures

  $so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions

  $so.IncludeIfNotExists = 1

  Invoke-sqlcmd -Database tempdb -Query "select name from MyDB.sys.objects (nolock) where [type] = 'P' and name not like 'sp_MS%'" | foreach {$procs +=,$_.name}

  gci | %{if ($procs -contains $_.Name) {$Proc = $_.Name; $_.Script($so) + " GO " | out-file -filepath "C:\temp\08_SPs $timestamp.sql" -Append}}

  On SQL 2008R2 I was getting something like this:

  SET ANSI_NULLS ON

  SET QUOTED_IDENTIFIER OFF

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))

  BEGIN

  EXEC dbo.sp_executesql @statement = N'

  CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables]

  @TablesToCheck int

  AS

  ...

  Now I am getting the script below and it fails to run due to the missing "GO" statement before the "Alter" command. Is this a bug? How do I work this around? Is there a way to put the "GO" statement before "Alter" command?

  Thanks.

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_AnyDataInTables]') AND type in (N'P', N'PC'))

  BEGIN

  EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_AnyDataInTables] AS'

  END

  ALTER PROCEDURE [dbo].[aspnet_AnyDataInTables]

  @TablesToCheck int

• Roust_m

  SSCoach

  Points: 17408

  More actions

  October 15, 2014 at 6:55 pm

  #1752709

  Fixed this by adding the code below. Yet this is a bug in SQL 2014.

  $path = "A:MyDB\08_SPs $timestamp.sql"

  $word = "ALTER "

  $replacement = "GO `r`n ALTER "

  $text = get-content $path

  $newText = $text -replace $word,$replacement

  $newText > $path