Multiple ambiguous overloads found for "Script" and the argument count: "1"

• Giank

  SSC Veteran

  Points: 296

  More actions

  November 17, 2011 at 7:21 pm

  #252104

  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]

• SpringTownDBA

  SSCertifiable

  Points: 6917

  More actions

  November 17, 2011 at 9:19 pm

  #1409672

  If I had to guess, I'd say that either $db or $db.Rules is null.

• bruce 1565

  SSCrazy

  Points: 2574

  More actions

  November 18, 2011 at 7:19 am

  #1409903

  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?

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  November 18, 2011 at 11:57 am

  #1410117

  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

• Ali Bajwa

  Old Hand

  Points: 383

  More actions

  December 21, 2011 at 11:35 pm

  #1424589

  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