I am trying to run this powershell Script using PowerGUI

Question

# Script all tables,triggers,views, stored procedures and udf in the database
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$Server = $args[0]

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $Server

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$date = get-date -format "M-d-yyyy"

$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.ScriptSchema = $True
$scrp.Options.Triggers = $True
$scrp.Options.ContinueScriptingOnError = $True
IF ( $args[1] )

{ 
$db=$s.Databases[$args[1]]
IF ($db.ID -gt 4)
    {
        $formatname = $db.name
        $FileName = "H:\0\"+ $Server + "_" + $formatname + "_" + $date + ".SQL" 
        echo "Scripting from $Server. $args[1] database objects to $FileName"
        $scrp.Options.FileName = $FileName
        echo "Scripting Tables ..."
#       $scrp.script($($db.Tables))
        echo "Scripting Triggers ..."
#       foreach ($tables in $db.Tables)
#       {
#           foreach ($Trigger in $tables.Triggers)
#           {
#               $scrp.Script($Trigger)  
#           }
#       }
        echo "Scripting views ..."
        $views = $db.Views | where {$_.IsSystemObject -eq $false}
        if ($views -ne $null)
        {
            $scrP.Script($views)
        }
        Echo "Scripting StoredProcedures ..."
        $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq     $false}
        if ($StoredProcedures -ne $null)
        {
            $scrp.Script($StoredProcedures)
        }
        echo "Scripting UDFs ..."
        $UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -   eq $false}
        if ($UserDefinedFunctions -ne $null)
        {
            $scrp.Script($UserDefinedFunctions)
        }   
    }
}
ELSE
{

foreach ($db in $s.Databases)
{
    IF ($db.ID -gt 4)
    {
        $formatname = $db.name
        $FileName = "H:\scripts\" + $Server + "_" + $formatname + "_" + $date +    ".SQL" 
        echo "Scripting database objects to $FileName"
        echo "Scripting from $Server.$db.name database"
        $scrp.Options.FileName = $FileName
        echo "Scripting Tables ..."

        $scrp.script($($db.Tables))
        echo "Scripting Triggers ..."
        foreach ($tables in $db.Tables)
        {
            foreach ($Trigger in $tables.Triggers)
            {
                $scrp.Script($Trigger)  
            }
        }
        echo "Scripting views ..."
        $views = $db.Views | where {$_.IsSystemObject -eq $false}
        if ($views -ne $null)
        {
            $scrP.Script($views)
        }
        Echo "Scripting StoredProcedures ..."
        $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq   $false}
        if ($StoredProcedures -ne $null)
        {
            $scrp.Script($StoredProcedures)
        }
        echo "Scripting UDFs ..."
        $UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -  eq $false}
        if ($UserDefinedFunctions -ne $null)
        {
            $scrp.Script($UserDefinedFunctions)
        }
    }
}   
}

echo "Done"

I am getting this error:

Exception calling "Script" with "1" argument(s): "Script failed for Server 'corpbillingdata'. " At C:\Users\jlauf\Documents\SQL Helpers from Travis\scriptdatabase1\scriptdatabase1.ps1:77 char:16 + $scrp.script <<<< ($($db.Tables)) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

How are you calling the powershell ? are you passing the one argument with the powershell scripts. Please check this link http://stackoverflow.com/questions/5592531/how-to-pass-an-argument-to-a-powershell-script — Hiten004, Dec 06 '13 at 21:08

score 0 · Answer 1 · answered Dec 06 '13 at 21:25

0

Save this powershell on the computer with C:\ScriptsDBObjects.ps1
Then run on the PowerGUI's Powershell consol C:\ScriptsDBObjects.ps1 ServerName

enter image description here

answered Dec 06 '13 at 21:25

Hiten004

2,425
1
22
34

I am trying to run this powershell Script using PowerGUI

1 Answers1