CODEWITHSUNDEEP
sqlpowershell
bryane92
bryane92

Reputation: 59

Can you pass a SQL Server object through PowerShell?

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

Upvotes: 0

Views: 189

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

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"

Upvotes: 1

Related Questions