Calling a stored procedure in PowerShell to insert into SQL Server database

Question

I am attempting to call a stored procedure in a PowerShell script to insert data into a SQL Server table. The script runs fine with no errors, but the information that should be wrote to the table is not there. I'm not sure where the code doesn't work as i am fairly new to PowerShell. Any help with this would be appreciated.

function WriteData 
{
    param
    (
        [String] $Server,
        [int] $CheckId,
        [String] $Investigate
    )

    try
    {
         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
         $SqlConnection.ConnectionString = "Server=MyServer;DataBase=MyDatabase;Integrated Security=SSPI"
         $SqlConnection.open()
         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
         $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
         $SqlCmd.CommandText = "EXEC dbo.usp_InsertTest"

         $param1=$sqlcmd.Parameters.Add("@param1" , [System.Data.SqlDbType]::VarChar)
         $param1.Value = $param1
         $param2=$sqlcmd.Parameters.Add("@param2" , [System.Data.SqlDbType]::Int)
         $param2.Value = $param2
         $param3=$sqlcmd.Parameters.Add("@param3" , [System.Data.SqlDbType]::VarChar)
         $param3.Value = $param3

         $SqlCmd.Connection = $SqlConnection
         $tmp=$SqlCmd.ExecuteNonQuery()
         $SqlConnection.Close()
     }
     catch
     {
         if( $SqlConnection.State -eq "Open" )
         {
             $SqlConnection.Close()
         }
         return -1
     }
 }

 WriteData -Param1 Test -Param2 1 -Param3 HelloWorld

pabrams · Accepted Answer · 2016-07-07 14:30:38Z

4

This looks wrong:

$param1.Value = $param1
$param2.Value = $param2
$param3.Value = $param3

Try setting those values to what you actually want. For example

$param1.Value = 'hi'
$param2.Value = 1
$param3.Value = 'bye'

answered Jul 7, 2016 at 14:30

pabrams

1,16512 silver badges35 bronze badges

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

1 Comment

Cornflake2068 Over a year ago

I didn't even notice that. I think a fresh pair of eyes were needed. That has worked. Thanks!

Collectives™ on Stack Overflow

Calling a stored procedure in PowerShell to insert into SQL Server database

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