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

  • 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;
  • I tried above solution but still getting the same error.
  • It's working for me on both v4.0 and v5.0, so I'm not sure what the problem might be. The only thing I see is the way that you get the database name and the names of the individual objects. These are more easily derived from the object names, but as I said, what you had was working for me. Just as a test, try running this instead:

    $path = "D:\Projects\Scripts\"
    $ServerName = "."
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
    $serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $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

    $dbname = $db.name
    $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.name
    $OutFile = $objpath + $ObjName + ".sql"
    $outscript = "";
    foreach ($line in $objs.Script($so)) { $outscript += ($line + "`nGO`n") }
    $outscript | out-file $OutFile;
    }
    }
    }