PowerShell SQL Server Datatable extra "row"

Question

In PowerShell I have this function: function RunSQLServerQuery { param ( [string]$server = “.”, [string]$instance = $(throw “Database Name is Required”), [string]$query, $parameters=@{} )

$connection = new-object system.data.sqlclient.sqlconnection( `
    “Data Source=$server;Initial Catalog=$instance;Integrated Security=TRUE;”);
$connection.Open
$cmd=new-object system.Data.SqlClient.SqlCommand($query,$connection)
foreach($p in $parameters.Keys)
{
    [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
}
$dt=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($dt) | Out-Null

return $dt.Tables[0]

}

When I call it like this:
$MyTest = "select GetDate()"
$rows = RunSQLServerQuery "servername" "master" $MyTest @{}
$rows

I get the following results:
MemberType : Method
OverloadDefinitions : {System.Void Open()}
TypeNameOfValue : System.Management.Automation.PSMethod
Value : System.Void Open()
Name : Open
IsInstance : True

Column1 : 7/19/2011 2:14:13 PM

When I inspect the value of $dt.Tables[0] before exiting the function the value is as expected:
Column1 ------- 7/19/2011 2:16:13 PM

I can't figure out why this is. I have tried switching the return to return ,$dt.Tables[0], but that was to no avail. I can't figure out why PowerShell is adding this text when return back from the function. There has to be something easy that I am missing.

Boezo

Mike Shepard Mike Shepard · Accepted Answer · 2011-07-19T19:48:45

The problem is with the line $connection.open

It should read [void]$connection.open()

You're getting the open method as part of your output instead of invoking it.

PowerShell SQL Server Datatable extra "row"

1 Answers