I need your inputs on how to convert the output from a SQL query into a string in powershell. I am currently doing the following
function ExecuteFromScriptRowset($connectionParams, $file)
{
return (invoke-sqlcmd @connectionParams
-inputFile $file -MaxCharLength 8000000)
}
I use the above function to call a .sql file that does something like
SELECT Names FROM tblXXX
Here is the actual powershell code that I use to invoke the SQL stored proc
$output = ExecuteFromScriptRowset $someDB (Resolve-Path '.\selectXXX.sql')
The output is an array of System.Data.DataRow which I need to convert to a string array. I am currently trying out the following which is not working as intended
$formatOut = @()
for ($i=0; $i -le $output.Length; $i++)
{
$formatOut = $formatOut + [string]$output[$i]
}
Here is the output of $formatOut is after the for loop and this is not what I was looking for
$formatOut[0] = System.Data.DataRow
$formatOut[1] = System.Data.DataRow
$formatOut[2] = System.Data.DataRow
Could you please let me know what I need to do so that I create a string array of the contents of the $output object
What you are seeing is completely normal, because by default ToString
will output object type. This is per .NET specifications.
You can convert a DataRow
to array of object using ItemArray
, and then join via "," or whichever join method you prefer, like this:
$formatOut = $formatOut + ($output[$i].ItemArray -join ",")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With