Can you pass a SQL Server object through PowerShell?

Question

I have a script that connects to a server instance and gets the SQL Agent Jobs for that server. I am trying to pass a SQL object from one function to another, but I continue to get errors. In this case I am running an integration test that passes the SQL object through PowerShell command line into my production script.

I've tried passing in just the server name, but it still does not seem to work.

Integration Script:

    $inst =  "Server01"
    $svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
    Write-Host ($svr | Format-Table | Out-String)   
    #Call Start stop script to add in the starts and stops
    powershell -command ". .\AddStart.ps1; Start-Step -svr $svr -jobName ""Test_AddStartStop_Integration"""

Production script:

function Start-Step($svr, $jobName)
{  
  $job = new-object ('Microsoft.SqlServer.Management.Smo.Agent.Job') 
 ($svr.JobServer, $jobName)
  Write-Host ($svr | Format-Table | Out-String)
}

Whenever I output the $svr object in the integration test I get all the connection info back. In Write-Host in Start.ps1 it just gives me back audit failure stuff. With the following error

powershell : new-object : Exception calling ".ctor" with "2" argument(s): "SetParent failed for Job

David Browne - Microsoft · Accepted Answer · 2019-10-08 17:49:59Z

1

You can't pass objects to a new instance of powershell. Instead "dot include" the script containing the functions and call them directly. EG:

. "$PSScriptRoot\Production.ps1"
$inst =  "localhost"
$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst
Write-Host ($svr | Format-Table | Out-String)   
#Call Start stop script to add in the starts and stops
Start-Step -svr $svr -jobName "Test_AddStartStop_Integration"

answered Oct 8, 2019 at 17:49

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bryane92 Over a year ago

HOLY MOLY. You did it! I had thought I was already doing the dot include in my PowerShell command, but like you said the new instance wasn't working. Thanks for being my light in this dark tunnel I was in.

Collectives™ on Stack Overflow

Can you pass a SQL Server object through PowerShell?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related