Powershell format output.

Hello everyone.

Hope someone could offer some advice on the issue I am trying to solve. I have a PowerShell script that calls a sql script. The SQL scripts checks disk size on the list of servers in the .txt file.

This is the script.

#script accepts 1 input paramters i.e a generic sql file 
#iterates through the list of instances
#this is a potentially dangerous script be careful!!!
#Assumptions – SQLCMD available , security on SQL Server
#input parameters

param ([string] $inputfile =$(throw "input file name is required"))
 
#global variables

#to add to the outputfilename
$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
#variable for outputfile name , based on inputfile and isodate
$outputfile=$inputfile + $isodate + ".csv"
#list of sql server instances 1 on each line
#iterate through the list and invoke a SQLCMD , with some format information
foreach ($svr in get-content "C:\SQLSkills\PowerShell\instances.txt"){
$rep = $svr -replace("\\","_")
invoke-expression "SQLCMD -E -S $svr -i $inputfile  -s ',' -W -h-1 >> $outputfile"

May question that I hope someone can help me with is how do I format the output into a table format with the column headers that are created in the .sql file ?

I call the script by : ./general.ps1 -inputfile "Server_Disk_Space.sql"

Any help appreciated as I am really stuck here and been trying to fix it for a few days with no success. If the script needs changeing or could work better please let me know as I am keen to learn.

Thanks everyone.

 

 

Parents
No Data
Reply
  • SQLCmd is a traditional command-line application and so will produce textual output rather than Powershell objects. If you are running this on a realtively modern SQL server, you may already have the Invoke-SQLCmd cmdlet which will return custom objects that you can manipulate or you can use native methods like .NET's System.Data.SqlClient. 

    technet.microsoft.com/.../hh289310.aspx

Children
No Data