CODEWITHSUNDEEP
powershellpowershell-2.0output-parameter
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

Multiple parameter in powershell

I have a powershell script to call the SP which is shown below

 $sqlTableConnection = New-Object [System.Data.SqlClient.SqlConnection]("Server=1xx.xx.xxx; Database=dbName; Integrated Security= True");

    $sqlTableCmd = New-Object [System.Data.SqlClient.SqlCommand]
    $sqlTableCmd.CommandText = "[System_Control].[usp_Check_system]"
    $sqlTableCmd.Connection = $sqlTableConnection;
    $jobName1 = "MASTER_JOB";
    $sqlTableCmd.Parameters.AddWithValue("@JobName", $jobName1) ;
    $outParameter = new-object System.Data.SqlClient.SqlParameter;
    $outParameter.ParameterName = "@JobSatatus";
    $outParameter.Direction = [System.Data.ParameterDirection]'Output';
    $outParameter.DbType = [System.Data.DbType]'Boolean';
    $sqlTableCmd.Parameters.Add($outParameter) ;
    $sqlTableCmd.Connection = $sqlTableConnection
     $sqlTableConnection.Open();
    $sqlTableCmd.ExecuteNonQuery();
    $truth = $sqlTableCmd.Parameters["@JobSatatus"].Value;
    $sqlTableConnection.Close();
    $truth;

but when I am running this I am getting below error

Exception calling "ExecuteNonQuery" with "0" argument(s): "The variable name '@
JobName' has already been declared. Variable names must be unique within a quer
y batch or stored procedure."
At line:15 char:33
+     $sqlTableCmd.ExecuteNonQuery <<<< ();
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Where I am doing mistake?

Upvotes: 0

Views: 2183

Answers (1)

JoeB
JoeB

Reputation: 1618

You should only be getting that error if you aren't re-initializing the SqlCommand object and so it has @JobName already defined.

I suspect you are getting this when you run it manually, not when you execute the script as a whole..

OR, if you are seeing it all the time, then maybe you moved this code into a function but the original $sqlTableCmd was initialized in the main code and now when this executes, it is in a different scope, but is getting confused by the one that was defined in the higher scope?

As I understand it, this line here is the one that should be setting things up fresh for you:

$sqlTableCmd = New-Object [System.Data.SqlClient.SqlCommand]

Upvotes: 1

Related Questions