Invoke-Sql Achoc Connection Closed Error

Mar 22, 2011 at 4:51 AM

Team CodePlex,

If you see the function below, when we supply Adhoc Connection parameters and not the active open connection to the function, it closes the connection and then tries to execute the command that errors out as following:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."

function invoke-sql{
param([Parameter(Position=0, Mandatory=$true)][string]$sql,
      [Parameter(ParameterSetName="SuppliedConnection",Position=1, Mandatory=$false)][System.Data.SqlClient.SQLConnection]$connection,
      [Parameter(Position=2, Mandatory=$false)][hashtable]$parameters=@{},
      [Parameter(Position=3, Mandatory=$false)][hashtable]$outparameters=@{},
      [Parameter(Position=4, Mandatory=$false)][int]$timeout=30,
      [Parameter(ParameterSetName="AdHocConnection",Position=5, Mandatory=$false)][string]$server,
      [Parameter(ParameterSetName="AdHocConnection",Position=6, Mandatory=$false)][string]$database,
      [Parameter(ParameterSetName="AdHocConnection",Position=7, Mandatory=$false)][string]$user,
      [Parameter(ParameterSetName="AdHocConnection",Position=8, Mandatory=$false)][string]$password,
      [Parameter(Position=9, Mandatory=$false)][System.Data.SqlClient.SqlTransaction]$transaction=$null)
 

       $cmd=new-sqlcommand @PSBoundParameters

       #if it was an ad hoc connection, close it
       if ($server){
          $cmd.connection.close()
       } 

       return $cmd.ExecuteNonQuery()
 
}

This works well with an active connection. Please let me know if I am doing anything wron here.

Regards

Amol

Coordinator
Mar 22, 2011 at 6:34 PM
You're right...that clearly looks like a pretty simple bug.

I'd make this change (and I will send it up tonight):

$results=$cmd.ExecuteNonQuery()

if ($server){

$cmd.connection.close()
}

return $results


On Mar 21, 2011 10:52pm, amolzagade <notifications@codeplex.com> wrote:
>
> From: amolzagade
>
> Team CodePlex,
>
> If you see the function below, when we supply Adhoc Connection parameters and not the active open connection to the function, it closes the connection and then tries to execute the command that errors out as following:
>
>
>
> Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery requires an open and available Connection. The connection's current state is closed."
>
>
>
>
> function invoke-sql{
>
> param([Parameter(Position=0, Mandatory=$true)][string]$sql,
>
> [Parameter(ParameterSetName="SuppliedConnection",Position=1, Mandatory=$false)][System.Data.SqlClient.SQLConnection]$connection,
>
> [Parameter(Position=2, Mandatory=$false)][hashtable]$parameters=@{},
>
> [Parameter(Position=3, Mandatory=$false)][hashtable]$outparameters=@{},
>
> [Parameter(Position=4, Mandatory=$false)][int]$timeout=30,
>
> [Parameter(ParameterSetName="AdHocConnection",Position=5, Mandatory=$false)][string]$server,
>
> [Parameter(ParameterSetName="AdHocConnection",Position=6, Mandatory=$false)][string]$database,
>
> [Parameter(ParameterSetName="AdHocConnection",Position=7, Mandatory=$false)][string]$user,
>
> [Parameter(ParameterSetName="AdHocConnection",Position=8, Mandatory=$false)][string]$password,
>
> [Parameter(Position=9, Mandatory=$false)][System.Data.SqlClient.SqlTransaction]$transaction=$null)
>
>
>
> $cmd=new-sqlcommand @PSBoundParameters
>
> #if it was an ad hoc connection, close it
>
> if ($server){
>
> $cmd.connection.close()
>
> }
>
> return $cmd.ExecuteNonQuery()
>
>
>
> }
>
> This works well with an active connection. Please let me know if I am doing anything wron here.
>
> Regards
>
> Amol
>
>
> Read the full discussion online.
> To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
> To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
> You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
> Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com
>
>
Coordinator
Mar 23, 2011 at 5:06 AM

I've uploaded changeset 59024 which fixes this and also a goofy typo in the help for new-connection.


Thanks for letting us know about this bug!

Mar 23, 2011 at 5:10 AM

Thanks Mike for you immediate assistance.