Error passing a DataTable object back from Powershell function

Viewed 11

I have a Powershell function to run sql queries against a connection that looks like this:

Function Invoke-SqlQuery {
    Param(
        [System.Data.SqlClient.SqlConnection]$SqlConnection,
        [String]$QueryText
    )

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection
    $SqlCmd.CommandText = $QueryText

    # Try/Catch here
    $Result = $SqlCmd.ExecuteReader()
    $ResultTable = New-Object System.Data.DataTable
    $ResultTable.Load($Result)

    return $ResultTable
}

I am trying to create a DataTable using this function in another part of the code:

$MyData = Invoke-SqlQuery -SqlConnection $MyConn -QueryText "SELECT * FROM dbo.Table"

When I run this I get the below error:

Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Data.DataTable".
At C:\Users\CameronJones\git\lsmi-etl\lsmi-etl\Main.ps1:26 char:1
+ [System.Data.DataTable]$StoreTable = Invoke-SqlQuery -SqlConnection $ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
    + FullyQualifiedErrorId : RuntimeException

When I query the type of $MyData it returns Object[]. How do I get the function to return a Datatable object?

EDIT: I tried omitting the return keyword from the function but I get the same result

0 Answers
Related