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