Multiple parameter in powershell

Question

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?

score 1 · Accepted Answer · answered May 06 '13 at 21:01

1

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]

answered May 06 '13 at 21:01

JoeB

1,538
1
11
9

Absolutely right.. I have changed the connection and command object names and things are working well now.. I ain't sure why this has happened as I was running the code on a separate window :o – Zerotoinfinity May 07 '13 at 07:42
1

If you are running it through the ISE, all the windows are considered the same scope I believe. I've noticed a function I load in via script-1 (or a variable declared) is still accessible in script-2 if they are all in the same ISE. Pretty scary actually. That is in v2, so not sure if it gets fixed in v3. – JoeB May 08 '13 at 19:41

Multiple parameter in powershell

1 Answers1