D B
asked on
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)?
$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)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then your SQL query creates more than just one table with several rows.
For reference, try this query (with the correct server, of course):
If so, try it like this:
Now run (obviously with a target path of your choice):
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
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
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
Check if the file really doesn't contain sensitive information, and attach it here.
ASKER
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
ASKER
Missing a '{' :-)
Thanks.
Thanks.
ASKER
Open in new window