locked
PowerShell Scripting Issue RRS feed

  • Question

  • System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
    $serverInstance = "."
    $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $serverInstance
    $jobs = $server.JobServer.Jobs
    #$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}

    if ($jobs -ne $null)

    {

    $serverInstance = $serverInstance.Replace("\", "-")

    ForEach ( $job in $jobs )
    {

    $FileName = "D:\Backup_scripts\SQLAgentJobs\" + $job.Name + ".sql"

    $job.Script() | Out-File -filepath $FileName

    }

    }

    When the above script executes, I am getting following error. Can you please help with the fix?

    Out-File : Illegal characters in path.
    At line:17 char:17
    + $job.Script() | Out-File -filepath $FileName
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OpenError: (:) [Out-File], ArgumentException
        + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand
    Out-File : The given path's format is not supported.
    At line:17 char:17
    + $job.Script() | Out-File -filepath $FileName
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : OpenError: (:) [Out-File], NotSupportedException
        + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.OutFileCommand


    Victor

    Thursday, December 19, 2019 3:03 AM

All replies

  • $job.Name has illegal characters in it. You have to fix that. We cannot fix it for you.


    \_(ツ)_/

    Thursday, December 19, 2019 3:05 AM
  • Well, we might be able to. It all depends on whether or no the correction might produce a duplicate file name in the directory -- but adding more code to test for that isn't hard, and correcting it might be just to add a sequence number to the end of the file name.

    This should remove all the invalid characters: 

    $BadCharacters = '[{0}]' -f [regex]::Escape(([IO.Path]::GetInvalidFileNameChars() -join ''))
    
    $Filename = "D:\Backup_scripts\SQLAgentJobs\" + $job.Name + ".sql"
    $FileName = $FileName -replace $BadCharacters, ""


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, December 19, 2019 4:46 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, December 26, 2019 8:36 AM
  • Yes, I came up with a slightly different approach. Used CmdExec option inside sql server agent job's step and my powershell script ran fine without any issues. Bit odd when I use PowerShell option inside sql server agent job's step. Basically, I used powershell.exe d:\PSScripts\MyTestscript.ps1 which worked fine.

    Thanks.

    Victor


    Victor

    Thursday, December 26, 2019 5:01 PM