How do you script out SQL Server agent jobs to single or individual files

Question

I've tried various Powershell scripts but they fail with:

The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a Transact-SQL statement or batch.".

At H:\Create_SQLAgentJobSripts2.ps1:89 char:22

• foreach ($job in $s.JobServer.Jobs)  
  

•                  ~~~~~~~~~~~~~~~~~  
  

• CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
• FullyQualifiedErrorId : ExceptionInGetEnumerator

What has gone wrong or how can I get better debugging on this error?

I executed this script:

.\Create_SQLAgentJobSripts2.ps1 .\ServerNameList.txt

Here's the script

param([String]$ServerListPath)

#write-host "Parameter: $ServerListPath"
 
#Load the input file into an Object array
$ServerNameList = get-content -path $ServerListPath

#Load the SQL Server SMO Assemly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

#Create a new SqlConnection object
$objSQLConnection = New-Object System.Data.SqlClient.SqlConnection

#For each server in the array do the following.
foreach($ServerName in $ServerNameList)
{
    Write-Host "Beginning with Server: $ServerName"

    Try
    {
        $objSQLConnection.ConnectionString = "Server=$ServerName;Initial Catalog=CED_NCT_RESOURCE_TRACK;Persist Security Info=True;User ID=CEDNCTAdmin;Password=CEDNCTAdmin;"
            Write-Host "Trying to connect to SQL Server instance on $ServerName..." -NoNewline
            $objSQLConnection.Open() | Out-Null
            Write-Host "Success."
        $objSQLConnection.Close()
    }
    Catch
    {
        Write-Host -BackgroundColor Red -ForegroundColor White "Fail"
        $errText =  $Error[0].ToString()
            if ($errText.Contains("network-related"))
        {Write-Host "Connection Error. Check server name, port, firewall."}
 
        Write-Host $errText
        continue
    }

    # Won't be using this object again
    Remove-Variable -Name objSQLConnection
 
    #If the output folder does not exist then create it
    $OutputFolder = ".\$ServerName"
    if (!(Test-Path $OutputFolder))
    {
        write-host ("Creating directory: " + $OutputFolder)
        New-Item -ItemType directory -Path $OutputFolder
    }
    else
    {
        write-host ("Directory already exists: " + $OutputFolder)
    }

    write-host "File: $(".\$OutputFolder\" + $($_.Name -replace '\\', '') + ".job.sql")"

    # Connect to the instance using SMO
    $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

    write-host ("SQL Server Edition: " + $s.Edition)
    write-host ("SQL Agent ErrorLogFile: " + $s.JobServer.ErrorLogFile)
    
    # Instantiate the Scripter object and set the base properties
    $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($ServerName)

    write-host ("SCRP ToString():" + $scrp.ToString())
    write-host ("Test scrp - Server: " + $scrp.Server)

    #The next step is to set the properties for the script files:
    $scrp.Options.ScriptDrops = $False
    $scrp.Options.WithDependencies = $False
    $scrp.Options.IncludeHeaders = $True
    $scrp.Options.AppendToFile = $False
    $scrp.Options.ToFileOnly = $True
    $scrp.Options.ClusteredIndexes = $True
    $scrp.Options.DriAll = $True
    $scrp.Options.Indexes = $False
    $scrp.Options.Triggers = $False
    $scrp.Options.IncludeIfNotExists = $True

    #Now, we can cycle through the jobs and create scripts for each job on the server.

    # Create the script file for each job
    foreach ($job in $s.JobServer.Jobs)
    {
        $jobname = $job.Name
        
         write-host ("Job: " + $jobname)

        $jobfilename = ($OutputFolder + "\" + $jobname + ".job.sql")
        $scrp.Options.FileName = $jobfilename

         write-host "Filename: $jobfilename"

        #This line blows up
        $scrp.Script($job)
    }
}

Answer 1

Possibly you're not instantiating the Server object correctly. Try the following instead...

# Alternative 1: With servername and port, using Trusted Connection...
$ServerName = 'YourServerName,1433'
$ServerConnection = New-Object  Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList @( $ServerName )

# Alternative 2: With an SqlConnection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$ServerName;Initial Catalog=CED_NCT_RESOURCE_TRACK;Persist Security Info=True;User ID=CEDNCTAdmin;Password=CEDNCTAdmin;"
$SqlConnection.Open() | Out-Null
$ServerConnection = New-Object  Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList @( $SqlConnection )

# Then...
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList @( $ServerConnection )
$Server.JobServer.Jobs | ForEach-Object {
    Write-Host "Job: $($_.Name)"
}