nedelja, 22. november 2015

Powershell save SQL data into Excel


When talking which queries are used  a lof of them are checcking statistics, execution as a job and saving data in table. After test is completed all this data is then converted in:
 - html
 - excel
 - svn.

HTML is very usefull, easy to open, but do not give you a possibility to sort data. .svn pattern gives you a posibility to open file in kibana and sort, compare data, etc. Excel great is after testing is completed. It is to sort data compare with different version of application, creating graphs,etc. In this section will be described export to excel.


In our case will be used statistics of most expensive stored procedures. There a over 100 queries to analyze statistics, latches, blocking processes, etc. SQL:

    SELECT TOP 1000 d.object_id
         ,OBJECT_NAME(object_id, database_id) 
    FROM sys.dm_exec_procedure_stats AS d
    WHERE db_Name(d.database_id)='yourdatabase'

This query search for all executed stored procedures in the test. Most important  is information is average elapsed time, which identify, which tables are not optimized for searching, inserting, deleting
Additional information about returned data from query will not be explained.

Connect to SQL

 Powershell is script language which has a lot common with .NET framework. On internet there is a lot help available how to connect to Mssql and execute query. For connection to MSSQL server is used connection string. Used will be used SQLClient class in the System.Data namespace. First will be instantiate a new SqlConnection and SqlCommand object.:

    $connectionString = "Server=$databaseServer;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

   $command = $connection.CreateCommand()   
    $command.CommandText  = "SELECT TOP 1000 d.object_id
        , OBJECT_NAME(object_id, database_id) 
    FROM sys.dm_exec_procedure_stats AS d
    Where db_Name(d.database_id)='Database'
    ORDER BY  d.last_elapsed_time DESC ; "


In some cases SQL execution takes more than expected, default timeout is 30 seconds to extend timeout use next command:
    $command.CommandTimeout = 300         

To create an in-memory table that can easily be manipulated is used SqlDataAdapte. The SqlDataAdapter will fill a variable $DataSet with the data from given sql statement from SQL.     
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet


Now we have extracted data from sql saved in the array table, it's time to prepare excel file. If folder does not exists then should be created:
    $fileXls ="$global:nasDepot\SQLReports\SPExpensiveQueries.xls"
    if (-Not (Test-Path "
$global:nasDepot\SQLReports") )
        New-Item "$ReportFileFolder\SqlReports" -ItemType directory > $null

   To create an excel file is used object
OfficeOpenXml.ExcelPackage with excel filename. If file does not exists then header in table is created starting with row in excel 1. New worksheet ExpensiveSP is created with method $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP").
Updating cell in the sheet is used method Cells with parameters: "$column$row". Header can be populated with different approach.

    $intRow = 1
    $ExcelPackage = New-Object OfficeOpenXml.ExcelPackage  $fileXls
    if (-Not (Test-Path $fileXls) )
        $Worksheet = $ExcelPackage.Workbook.Worksheets.Add("ExpensiveSP")
        $TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1
        $Worksheet.Cells["A$intRow"].Value="INSTANCE NAME:"
        $Worksheet.Cells["B$intRow"].Value= $connectionString
        $intRow ++
        $Worksheet.Cells["A$intRow"].Value="Server Name:"
        $Worksheet.Cells["B$intRow"].Value= $dataSource
        $intRow ++
        $intRow ++
        $Worksheet.Cells["A$intRow"].Value = "ObjectId"
        $Worksheet.Cells["B$intRow"].Value = "DatabaseId"
        $Worksheet.Cells["C$intRow"].Value = "Object Name"
        $Worksheet.Cells["D$intRow"].Value = "DateTime"
        $Worksheet.Cells["E$intRow"].Value = "Averega response time (microsecond)"
        $Worksheet.Cells["F$intRow"].Value = "Execution count"
        $Worksheet.Cells["G$intRow"].Value = "TestName"
        $Worksheet.Cells["H$intRow"].Value = "Version"
        $Worksheet.Cells["I$intRow"].Value = "Comment"
        $intRow ++
        $Worksheet = $ExcelPackage.Workbook.Worksheets.Item("ExpensiveSP")
        $dimAddress = $Worksheet.Dimension.Address

When header was completed now data from variable $DataSet  saved in excel. First should be identified end row to continue adding data from $DataSet that values in excel will not be updated. 
    for($i=0;$i -le $DataSet.Tables[0].Rows.Count-1;$i++)
        $a = $($DataSet.Tables[0].Rows[$i][0])  
            #write-host "value is : $i $a $($DataSet.Tables[0].Rows[$i][1])$($DataSet.Tables[0].Rows[$i][2])"
            $Worksheet.Cells["A$intRow"].Value = $($DataSet.Tables[0].Rows[$i][0])
            $Worksheet.Cells["B$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][1])"
            $Worksheet.Cells["C$intRow"].Value = $($DataSet.Tables[0].Rows[$i][2])
            $Worksheet.Cells["D$intRow"].Value = "$($DataSet.Tables[0].Rows[$i][3])"
            $Worksheet.Cells["E$intRow"].Value =$($DataSet.Tables[0].Rows[$i][4])
            $Worksheet.Cells["F$intRow"].Value = $($DataSet.Tables[0].Rows[$i][5])
            $Worksheet.Cells["G$intRow"].Value = "$global:TestName $global:TestCaseName $global:TestScenario"
            $Worksheet.Cells["H$intRow"].Value = $global:Version
            $Worksheet.Cells["I$intRow"].Value = "$global:additional_comment"


After excel worksheet is populated with data from sql statement now table should be formatted and saved.  



Powershell is gives a great opportunity to process data and show them in reports. This blog show how data is retrieved from SQL and saved into Excel. 


Ni komentarjev:

Objavite komentar