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)?

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D B

ASKER

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

oBdA

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.

D B

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

Open in new window

D B

ASKER

Missing a '{' :-)
Thanks.