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.

 

 

  • Is it just generic disk size you are looking at, or actual SQL type information that specifically needs you to execute a SQL command? If it's the first, I don't see the point of using SQL.  If not, can you give the SQL script so we can getter a better idea what's actually happening?

    Also, you can use PowerShell & .NET for invoking SQL commands, and you can then fill a dataset with this information, which makes it a lot easier to format as required.

     

  • 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

  • hi Tim

    thanks for the reply. This is the script I am calling.  Works fine in SSMS , and wanted to achieve the same across a list of servers via PowerShell.

    DECLARE @hr int
    DECLARE @fso int
    DECLARE @drive char(1)
    DECLARE @odrive int
    DECLARE @TotalSize varchar(20)
    DECLARE @MB bigint ; SET @MB = 1048576
    CREATE TABLE #drives (drive char(1) PRIMARY KEY,
    FreeSpace int NULL,
    TotalSize int NULL)
    INSERT #drives(drive,FreeSpace)
    EXEC master.dbo.xp_fixeddrives
    EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    DECLARE dcur CURSOR LOCAL FAST_FORWARD
    FOR SELECT drive from #drives
    ORDER by drive
    OPEN dcur
    FETCH NEXT FROM dcur INTO @drive
    WHILE @@FETCH_STATUS=0
    BEGIN
    EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
    UPDATE #drives
    SET TotalSize=@TotalSize/@MB
    WHERE drive=@drive
    FETCH NEXT FROM dcur INTO @drive
    END
    CLOSE dcur
    DEALLOCATE dcur
    EXEC @hr=sp_OADestroy @fso
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
    SELECT @@servername as ServerName, drive,
    FreeSpace as 'Free(MB)',
    TotalSize as 'Total(MB)',
    CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
    GETDATE() as Date_Entered
    FROM #drives