Stuart Moore

Musings of a Data professional

Stuart Moore

Calling a PowerShell function in a Start-Job script block when it’s defined in the same script

While writing some scripts some upcoming SQL Server User Group presentations I had to remind myself of how to do something in PowerShell, and thought I’d put a reminder up here for myself and for anyone else who needs it.

The problem was that I wanted to show firing off multiple SQL Server backup jobs asynchronously from within a PowerShell script. Sounds nice and simple, write some custom modules, and then fire them off with Start-Job as required. And while that’s the best way to do things (PowerShell rule1: Write functions, not scripts), in this case I wanted to keep everything in one script as it would make it easier when presenting

So the basics set up is:

function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}

while($i>$x){
    Start-Job -ScriptBlock <WhatDoIPutHere?>
}

Normally I’d just add the other script or module in there, but what about the function? Well, the trick is to pass the function in as a variable to InitializationScript, and then call it in ScriptBlock like so:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {backup_db_id} -InitializationScript $func
}

So that’s the first step. But I also want to pass through the 2 variables, so I’ll need to parametise the function pass. Which means stacking things like this:

$func = {function backup_db_id
{
param([int]$m, [int]$md)

import-module "SQLPS" -DisableNameChecking
...Do stuff...
}
}

while($i>$x){
    Start-Job -ScriptBlock {param($tm,$ti) backup_db_id $tm $ti } -InitializationScript $func -ArgumentList($x,$i)
    $i++
}

And now everything is passed through happily.

The other thing to note from the function definition here, is that I’ve had to include the Import-Module to load the SQLPS extensions as PowerShell will be starting up a new instance for every Start-Job and that instance won’t have the SQLPS extensions loaded up (unless you’ve set it as a system wide default, but I don’t always want to rely on that).

Previous

Upcoming SQL Server User Group sessions I’m speaking at

Next

Getting Perfmon Data from SQL Server into R for analysis

13 Comments

  1. Dan Denis

    This is the first, and only accurate example of executing a job with this scenario I have found, both on-line and in any reference book I have come across so far.

    • Stuart Moore

      Thank You. Glad you’ve found it useful.

      • Arindam

        Hi,

        I tried following your method of defining Function within scriptblock and calling it.
        But somehow, mine is not working. The script code is ok as I can use the code without Start-job and running as a synchronous operation and it works fine and updates database table.But while calling performing same action through function and calling it through Start-job, it fails.
        Please let me know, where I can send you my code for your reference.

        Thanks,
        Arindam

  2. DarkLite1

    Thank you, this was really a great help to me! 🙂

  3. DarkLite1

    Thank you, this was really a great help to me! 🙂

    I had to do it like this to be fully operational:

    Start-Job -ScriptBlock {Param($Target, $OlderThanDays, $Server) Delete-OldFiles $Target $OlderThanDays $Server} -InitializationScript $JobFunc -ArgumentList ($Target, $OlderThanDays, $Server) -Name DelFiles

    I’m now looking for a way to add my switch ‘-CleanFolders’ and then I’m all done.

    • Stuart Moore

      Excellent! It’s really great to hear it’s been of use.

      A commenter on another post suggested this for a way to control switches:
      if ($x -eq y){
      $RecoveryOption = $True
      }else{
      $RecoveryOption = $False
      }
      Restore-SQLDatabase -Database $DBName -BackupFile $backupfile -NoRecovery:$RecoveryOption

      It’s the colon that makes it work. Not sure if that’s an option for you?

      If you find a way to get it working I’d love to know.

  4. Jameson

    Is it possible to also initialize the function so it can be called from outside a background job?

    • Stuart Moore

      Sorry for the tardy reply, I’ve been moving house recently.

      I’m afraid I don’t quite understand what you are asking. The function could be initialized as a standard powershell function by removing the $func={} from around the function definitions. This would mean you could then just call it like any other function.

      If that’s not quite what you meant please let me know.

      Cheers
      Stuart

  5. Many thanks. Great article, got me one step further in my PS journey.

  6. Andrew L

    Nice work. That has helped me out a load.

    Cheers,

    Andrew

  7. Hello, I found this while working on an issue where I have used Get-Service on a list of computers and occasionally one will hang the whole task. I’m wanting to get status SQL servers. After 10 seconds I want it to give up and move on to the next one instead of hanging.

    I have tried your version and honestly exhausted my skills on variations. I’ve only been at this about 6 months.

    $timeout = 10
    $Server = “COMPUTERNAMEHERE”
    $Servicename=”mssqlserver”

    $func = {function do-this

    {[string]$Server, [string]$Servicename, [string]$SQLServiceStatus

    $SQLServiceStatus = Invoke-Command -ComputerName $Server -ScriptBlock {param($Server,$Servicename) Get-Service -computer $Server |Where-Object {$_.name -eq $Servicename}|foreach {$_.status} } -ArgumentList $Server, $Servicename, $SQLServiceStatus

    }
    }

    Start-Job -ScriptBlock {param($Server,$Servicename) do-this $Server,$Servicename } -InitializationScript $func -ArgumentList ($Server,$Servicename, $SQLServiceStatus)

    Wait-Job do-this -Timeout $timeout
    Stop-Job do-this
    Receive-Job do-this
    Remove-Job do-this

    write-host $Servicename “Status is” $SQLServiceStatus
    ——————————————————
    Should say mssqlserver Status is running

    • Stuart Moore

      Hi,
      I think you might be better off with a simpler version using Invoke-Command. I’m assuming you want to check for all running SQL Server instances on a group of servers? I use something similar to this to do that:

      $session = New-PSSession -ComputerName Server1, server2,server3 -ErrorAction SilentlyContinue
      $ServiceName = 'MSSQL`$'
      $timeout = 10
      $remote = Invoke-Command -Session $session -ScriptBlock {Get-Service -Name *$using:servicename*} -AsJob
      $remote | Wait-Job -Timeout $timeout
      $output = $remote | Receive-Job
      $output | ForEach {Write-Host "$($_.Name) on $($_.PsComputerName) is $($_.Status)"}

      Using Invoke-Command means there’s less overhead in building up the called function compared to Start-Job, and with the -AsJob switch it’s running as a job anyway.
      just a couple of comments which might help explain bits:
      I’ve changed the ServiceName to MSSQL$ (escaping teh $ with `) based on what I think you’re doing (though could be wrong).
      I’m using the using scope modifier to tell Invoke-Command to using the variable $servicename in the calling scope (https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_scopes?view=powershell-6&viewFallbackFrom=powershell-5)

      Hope that’s useful? Sometimes it’s easy to make something more complicated than it needs to be.

      Let me know that’s not right or anything doesn’t make sense

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén