Powershell Output SQL Query Results to File

I am running a query in a PowerShell loop and after each execution, I want to write the results to a file. I am using the following:

$accounts = invoke-sqlcmd -ServerInstance $server -Database $database -Query "$sql"
$accounts | Add-Content -Path $outTextFile

but the contents of the file is just n rows (one for each data row) that all contain "System.Data.DataRow" (without the quotes).
I want to write the output (a single column) with no formatting, no headers, just the values returned from the query.

Also, I don't know if the query will add any padding to the end of the results, but is there some way I can force the output to be a specific length (in this case, 26 bytes + CRLF)?
LVL 15
dbbishopAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
There are several ways to do that.
The following examples will format the output to a length of 26, right-aligned; for left-alignment, replace the 26 with -26.
You can access index 0 of the respective row:
$accounts | % {"{0, 26}" -f $_[0]} | Add-Content -Path $outTextFile

Open in new window

Or you can select and expand the column name you want; replace "<name>" with the actual column name:
($accounts | Select-Object -ExpandProperty <name>) | % {"{0, 26}" -f $_} | Add-Content -Path $outTextFile

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:
Both give errors:
Error formatting a string: Input string was not in a correct format..
At D:\CABDataImport\common\Utilities\Create NFS Account Extract.ps1:49 char:17
+     $accounts | % {"0, 26}" -f $_[0]} | Add-Content -Path $outTextFile
+                    ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (0, 26}:String) [], RuntimeException
    + FullyQualifiedErrorId : FormatError


Error formatting a string: Input string was not in a correct format..
At D:\CABDataImport\common\Utilities\Create NFS Account Extract.ps1:50 char:57
+     $accounts | Select-Object -ExpandProperty account | % {"0, 26}" -f $_} | Add-Co ...
+                                                            ~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (0, 26}:String) [], RuntimeException
    + FullyQualifiedErrorId : FormatError

Open in new window

0
oBdACommented:
Then your SQL query creates more than just one table with several rows.
For reference, try this query (with the correct server, of course):
 $DBNames = Invoke-Sqlcmd -ServerInstance <SQLServer> -Database master -Query "select name, recovery_model_desc from sys.databases"
$DBNames | % {"{0, 26}" -f $_[0]} | Write-Host -Fore Yellow
($DBnames | Select-Object -ExpandProperty name) | % {"{0, -26}" -f $_} | Write-Host -Fore Green

Open in new window

Do you see the accounts when you just enter "$Accounts"?
If so, try it like this:
$accounts | Select-Object -ExpandProperty account | % {"{0, 26}" -f $_} | Add-Content -Path $outTextFile

Open in new window

If that still doesn't work, please add two dummy accounts to the table, change the query so that it will only return these two accounts, and set $Accounts to the results of the query.
Now run (obviously with a target path of your choice):
$Accounts | Export-CliXml C:\Temp\accounts.xml

Open in new window

Check if the file really doesn't contain sensitive information, and attach it here.
0
dbbishopAuthor Commented:
I'll try your suggestions but I guarantee you I am only returning a single column and a single recordset. My query is:
select left(account_id, 3) + cast(substring(account_id, 5, 6) as char(6)) + cast(' ' as char(10)) + retailer_id as account 
from table1 a 
inner join table2 ri
on di.client_id = db_name()
order by 1

Open in new window

0
dbbishopAuthor Commented:
Missing a '{' :-)
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.