November 17, 2011 at 7:21 pm
My script works when I run this through cmd prompt then type POWERSHELL to launch PS.
When I try to execute the same script inside a SQL Server Job I receive the error below:
A job step received an error at line 47 in a PowerShell script. The corresponding line is ' $scr.Script($Rules)'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Multiple ambiguous overloads found for "Script" and the argument count: "1". '. Process Exit Code -1. The step failed.
Any help is appreciated! Thanks in advance.
Actual Code
-------------------------------------------------------
#Start file
Set-ExecutionPolicy RemoteSigned
#Set-ExecutionPolicy -ExecutionPolicy:Unrestricted
#-Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | out-null
#$error.clear()
#$erroractionpreference = "Continue"
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $true
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $true
$options.ToFileOnly = $true
$options.AppendToFile = $true
#Set options for SMO.Scripter
$scr.Options = $options
#Rules
$options.FileName = $scriptpath +"\"+$dbname +"\5. "+ $dbname + "_Rules_Script.sql"
foreach($Rules in $db.Rules)
{
$scr.Script($Rules)
}
#Tables
$options.FileName = $scriptpath + "\"+$dbname +"\1. "+ $dbname + "_Tables_Script.sql"
foreach($tables in $db.Tables)
{
$scr.Script($tables)
}
#$scr.Script($db.Tables)
#Views
$options.FileName = $scriptpath + "\"+$dbname +"\3. "+ $dbname + "_Views_Script.sql"
$views = $db.Views | where {$_.IsSystemObject -eq $false}
if ($views -ne $null)
{
$scr.Script($views)
}
#StoredProcedures
$options.FileName = $scriptpath + "\"+$dbname +"\2. "+ $dbname + "_Procs_Script.sql"
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
if ($StoredProcedures -ne $null)
{
$scr.Script($StoredProcedures)
}
#Functions
$options.FileName = $scriptpath + "\"+$dbname +"\4. "+ $dbname + "_Functions_Script.sql"
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
if ($UserDefinedFunctions -ne $null)
{
$scr.Script($UserDefinedFunctions)
}
#Triggers
$options.FileName = $scriptpath + "\"+$dbname +"\6. "+ $dbname + "_Triggers_Script.sql"
foreach($tables in $db.Tables)
{
foreach($trigger in $tables.Triggers)
{
$scr.Script($trigger)
}
}
#DBTriggers
$options.FileName = $scriptpath + "\"+$dbname +"\7. "+ $dbname + "_DBTriggers_Script.sql"
$DBTriggers = $db.Triggers
if ($DBTriggers -ne $null)
{
$scr.Script($DBTriggers)
}
}
GenerateDBScript $args[0] $args[1] $args[2]
November 17, 2011 at 9:19 pm
If I had to guess, I'd say that either $db or $db.Rules is null.
November 18, 2011 at 7:19 am
No real answers here just questions. May be things you’ve already looked at.
1. What account is running the job, does it have the needed permissions?
2. Is the job type set as Powershell? What if you change it to Operating system and call Powershell and your script that way?
November 18, 2011 at 11:57 am
My script works when I run this through cmd prompt then type POWERSHELL to launch PS.
When you execute through a cmd shell are you on the same server as the SQL Server or some other server?
When I try to execute the same script inside a SQL Server Job...
Which version of SQL Server? If 2008+, are you using the PowerShell Job Step Type?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2011 at 11:35 pm
Hi GianK:
Looks like you are having permissions issue as suggested by Bruce. Please make sure that you give atleast MODIFY permission on the folder within which your powershell step creates the .sql script (ref: script $options.FileName = $scriptpath +"\"+$dbname +"\5. "+ $dbname + "_Rules_Script.sql") to the account under which the SQL Server Job will run.
Once you will do that, hopefully the job will finish successfully.
Kind Regards,
Ali Bajwa
SQL Server DBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply