I have a simple problem, but not simple clear example of solving it.  I have a PowerShell 2.0 script running on a Windows 2008 R2 server that is taking a list of SQL Server 2008 R2 servers and looping through the $Svr.Db.JobServer.Jobs collection to record the $Job.OwnerLoginName and a few other properties, and writes the data in variables.  Now I want to write these back to a MonitorSvr.DbADb.dbo.JobInfo table.  I wrote function to call a SQL stored procedure, "usp_Insert_Job_Record", to do the actual insert using input parameters.

My problem is how to invoke the stored procedure, populate the input parameters and execute the stored procedure with no results returned.  I looked at the Invoke-SQLCmd, but that didn't seem to do it.  I am currently trying to instantiate the stored procedure from $svr.db.StoredProedure.Items("usp_Insert_Job_Record"), but I don't have a clear example on how to populate the parameter collection and execute.  I imagine something like this:  

$SMOServer = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $SQLServerName 
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($SMOServer, "dbaAdmin")
$Jobs = $SMOServer.JobServer.Jobs 
foreach ($Job in $Jobs)
{
$spInsertJobRecord = $db1.StoredProcedures.Item("usp_Insert_Job_Record") 
$spInsertJobRecord.Param(0).Value = $SMOServer.Name 
$spInsertJobRecord.Param(1).Value = $PortNum
$spInsertJobRecord.Param(2).Value = $AgentSvcName 
$spInsertJobRecord.Param(3).Value = $Job.Name
$spInsertJobRecord.Param(4).Value = $Job.OwnerLoginName
$spInsertJobRecord.Param(5).Value = $Job.IsEnabled
$spInsertJobRecord.Param(6).Value = $Job.LastRunOutcome
$spInsertJobRecord.Param(7).Value = "ScanDate: " + $Today
$spInsertJobRecord.Execute
}
$SMOServer = $null 

A clear and simple example would be appreciated.  

Thanks!

Cheers!

Brandon

hi,

do you get any error?

$sql = "exec usp_Insert_Job_Record @ServerName = '$($SMOServer.Name)', @PortNum = $PortNum . . .

invoke-sqlcmd $sql

or use System.Data.SqlClient.SqlCommand directy. 

You can get a job info using T-SQL. 

Execute the SQL with the help of invoke-sqlcmd to store the result into table.

Refer this link

http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx

--Prashanth

Its not perfect, but it works.

function Call-Procedure
{
<#
.SYNOPSIS
    
    Calls a procedure using passed connectionstring and parameters. 
    
.DESCRIPTION
    
    Calls a procedure using passed connectionstring and parameters.  

.PARAMETER ConnectionString

    Connection String.   

.PARAMETER Procedure

    Procedure to execute. 
    
.PARAMETER Parameters

    An Array of parameters and        
        
.OUTPUTS 

    System.Data.DataSet

.EXAMPLE 
        
    Call-Procedure -ConnectionString:"Server=xxx;database=xxx;trusted_connection=true;Connection Timeout=20;" -Procedure:"xxx" -Parameters:@{Module="Set-Instance"; Exception="This is an Error"}
    
.NOTES 

     Revisions:

    Rev  Date         Who  			What
    ---  -----------  ------------  ---------------------------------------------
    000  03-24-2015   John Couch    Initial Revision

.LINK

    https://gist.github.com/jmosbech/5653796
#>
    [CmdletBinding()] 
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [string] $ConnectionString,
          [Parameter(Position=1, Mandatory=$true, ValueFromPipeline = $true)] [string] $Procedure,
          [Parameter(Position=2, Mandatory=$true, ValueFromPipeline = $true)] $Parameters=@{}) 

    Begin 
    { 
        [System.Data.SqlClient.SqlConnection] $SqlCN = New-Object System.Data.SqlClient.SqlConnection
        [System.Data.SqlClient.SqlCommand] $SqlCMD = New-Object System.Data.SqlClient.SqlCommand
        [System.Data.SqlClient.SqlDataAdapter] $SqlDA = New-Object System.Data.SqlClient.SqlDataAdapter
        [System.Data.DataTable] $SqlDT = New-Object System.Data.DataTable 
    } 
    Process
    {    
        try 
        {            	
	        $SqlCN.ConnectionString = $ConnectionString
            $SqlCMD.CommandType = [System.Data.CommandType]::StoredProcedure
	        $SqlCMD.Connection = $SqlCN
	        $SqlCMD.CommandTimeout = 300				      
	        $SqlCMD.CommandText = $Procedure

	        foreach($Parameter in $Parameters.Keys)
            {
 		        [Void] $SqlCMD.Parameters.AddWithValue("@$Parameter",$Parameters[$Parameter])
 	        }

	        $SqlCN.Open
	        $SqlDA.SelectCommand = $SqlCMD
	        [Void] $SqlDA.Fill($SqlDT)
	        $SqlCN.Close
        }
        catch [system.exception]
        {
            throw $_  		
        }
        finally 
        {
            if($SqlCN.ConnectionState -ne [System.Data.ConnectionState]::Closed) {$SqlCN.Close()}
	
            $SqlDA.Dispose()
            $SqlCMD.Dispose()
            $SqlCN.Dispose()
        }
    }
    End
    { 
        Write-Output @(,($SqlDT))
    } 
}

John,

This looks like what I was looking for.  the line [Void] $SqlCMD.Parameters.AddWithValue("@$Parameter",$Parameters[$Parameter]) was what I wasn't seeing in any documentation.  When I get a few minutes, I'll try it and report back the results.

Cheers!

Brandon Forest