I use the following PowerShell function to run an arbitrary SQL query against an SQL Server instance, then return whatever data is there:
Function ExecSQLReader([string] $sqlText) {
$cn = New-Object System.Data.SqlClient.SqlConnection
$cn.ConnectionString = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI;" -f $fc.server,$fc.database
$cn.Open()
$cmd = $cn.CreateCommand()
$cmd.CommandText = $sqlText
$cmd.CommandTimeout = 60
$resTable = New-Object("System.Data.DataTable")
try {
$result = $cmd.ExecuteReader()
$resTable.Load($result)
$result.Close()
$cn.Close()
} catch {
"=======ERROR in ExecSQLReader========" | DoLog
"Message: {0}" -f $_.Exception.Message | DoLog
"SQL: {0}" -f $sqlText | DoLog
"=======/ERROR in ExecSQLReader=======" | DoLog
}
return($resTable)
}
The function works fine when there is data returned by the query.
However, the function does not return any column-related info when there is no data returned by the query.
I need to know column names / types / order etc even if there is no data rows.
I can see that the returned value is different based on resulting record count (which makes things even more complicated):
So, the question is: how to make sure that the returned object is always of a "System.Data.DataTable" type?
Or, at least, how to make sure that columns-related information (names, types, order) is always available in function results, regardless of the number of rows returned by the query?
Any hints?
This is a common gotcha when returning a collection from a PowerShell function. As a workaround, add a comma before the return value:
return(,$resTable)
If no rows are returned by the query, the DataTable will contain the source query schema but with zero rows.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments