PowerShell script failing while scripting SQL objects from SQL Parallel data warehouse but working fine for On-premise SQL server.

$path = “D:\Projects\Scripts\”

$ServerName = “.”

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)

$serverInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $ServerName

#$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.UrnCollection') $ServerName


$db=$serverInstance.Databases["AdventureWorksPDW2012"]

$IncludeTypes = @(“tables”,”StoredProcedures”,”Views”,”UserDefinedFunctions”)

$ExcludeSchemas = @(“sys”,”Information_Schema”)


$so = new-object (‘Microsoft.SqlServer.Management.Smo.ScriptingOptions’)


$so.IncludeIfNotExists = 0

$so.SchemaQualify = 1

$so.AllowSystemObjects = 0

$so.ScriptDrops = 0

 

#$dbs=$serverInstance.Databases

#foreach ($db in $dbs)

#{

$dbname = “$db”.replace(“[“,””).replace(“]”,””)

$dbpath = “$path”+”$dbname” + “\”

if ( !(Test-Path $dbpath))

{$null=new-item -type directory -name “$dbname”-path “$path”}

 

foreach ($Type in $IncludeTypes)

{

$objpath = “$dbpath” + “$Type” + “\”

if ( !(Test-Path $objpath))

{$null=new-item -type directory -name “$Type”-path “$dbpath”}

foreach ($objs in $db.$Type)

{

If ($ExcludeSchemas -notcontains $objs.Schema )

{

$ObjName = “$objs”.replace(“[“,””).replace(“]”,””)

$OutFile = “$objpath” + “$ObjName” + “.sql”

$objs.Script($so)+”GO” | out-File $OutFile #-Append    --- Failing here

#Write-Host($objs)


}

}

}

#}

Exception calling "Script" with "1" argument(s): "Script failed

This script is working perfectly for on-premise SQL server but giving issue for Parallel data warehouse. Can someone please help as I am new to PowerShell. Thanks in advance

Parents
  • Keep in mind that "$objs.Script($so)" is not a string but a collection of objects. So, you need to treat it like the collection of SQL statements that it is (even if it's only a collection of one string):

    $outscript = "";
    foreach ($line in $objs.Script($so)) { $outscript += ($line + "`nGO`n") }
    $outscript | out-file $OutFile;
Reply Children