Author name: Tim van Kooten Niekerk

PowerShell WebService Calls

/ PowerShell Scripting /

Performing a SOAP web service call using PowerShell. The methods can be requested by means of the command: $oWebSvc1 | Get-Member.

#WebService values...
[string]$sWebSvcWSDL = "https://host.domain.fqdn/Webservice1?wsdl"
[string]$sPostValue1 = "Waarde"
[string]$sPostValue2 = $true

#Create webservice object...
$oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL

#POST values w. result in een object...
$oResult1 = $oWebSvc1.method1($sPostValue1, $sPostValue2)

#POST values w. result to RAW XML...
[xml]$xResult = $oWebSvc1.method1($sPostValue1, $sPostValues2) | ConvertTo-Xml
$xResult.get_OuterXML() | Out-File C:\Pad\Naar\Bestand.xml

PowerShell Add SQL Modules

/ PowerShell Scripting /

Modules can now be added by using Import-Module. The following example list all the available.

Get-Module -ListAvailable

Load the SQLPS module (if available)….

Import-Module -Name "SQLPS"

Before you can import the SQLPS powershell module you need to install SQL Management Studio or install the SQLSysCLRTypes, SharedManagementObjects and PowerShellTools packages from the SQL Server Feature Pack.

MSSQL Get Settings and Counters w. PowerShell

/ MSSQL Server / 
# Query Server Configuration...
Invoke-Sqlcmd -Query "SELECT * FROM sys.configurations WHERE description LIKE '%server memory%'"

# Server performance counter Page life expectancy (>(300s/4G))...
# Server performance counter Lazy writes/sec (take 2 samples with 1s between samples)...
Invoke-Sqlcmd -Query "SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Page life expectancy', 'Lazy writes/sec')"

MSSQL 2012 New T-SQL Features

/ MSSQL Server /

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1

MSSQL combine SUMS from 2 tables

/ MSSQL Server /

Below query combines the SUMS of two tables into a total SUM for a specific column.

WITH combiTable ([Table], xName, xValue)
AS ( SELECT 1 AS [Table], name, SUM(value) FROM Table1 GROUP BY name
     UNION
     SELECT 2 AS [Table], name, SUM(value) FROM Table2 GROUP BY name )
SELECT xName AS Name, SUM(xValue) AS Total FROM combiTable
GROUP BY xName
ORDER BY Total DESC
GO

XML Stylesheet Example

/ XML /

An XML stylesheet can be used to transform XML file to an HTML table. Check out the results of stylesheet below if it is linked to an XML file. In the source of the page you will only display XML.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >
  
  <xsl:template match="/">
    <html>
    <head><title/></head>
    <body>
      <table border="0pt" style="solid">
        <tbody>
          <!--call table headers-->
          <!--table headers-->
          <tr bgcolor="grey">
            <!--header-->
            <xsl:for-each select="manuals/manual[1]/*">
              <th><xsl:value-of select="name()"/></th>
            </xsl:for-each>
            <th>new-id</th>
          </tr>
          <!--call table rows-->
          <xsl:apply-templates select="manuals/manual">
            <xsl:sort order="ascending" select="title"/>
          </xsl:apply-templates>
        </tbody>
      </table>
    </body>
    </html>
  </xsl:template>

  <xsl:template match="manuals/manual">
    <!--table rows-->
    <!--kleur bepalen-->
    <xsl:variable name="kleur">
      <xsl:choose>
        <xsl:when test="category='PowerShell Scripting'">lightblue</xsl:when>
        <xsl:when test="category='MSSQL Server'">lightpink</xsl:when>
        <xsl:otherwise>white</xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <tr bgcolor="{$kleur}">
    <xsl:for-each select="*">
      <td><xsl:value-of select="."/></td>
    </xsl:for-each>
    <!--extra regel t.b.v. newid-->
    <td><xsl:value-of select="id+1000"/></td>
    </tr>
  </xsl:template>

</xsl:stylesheet>

MSSQL Select with XML Output

/ MSSQL Server /

The following query returns a result in XML format. the root1 option (root element) adds a root element named “root element” to the file. The option elements creates elements instead of attributes. The auto option creates standard elements with the table name and column names mapped to elements.

SELECT * FROM [tablename] FOR XML auto, root('rootelement'), elements

Another example with a custum (nested) XML layout…

-- Retrieve instancename and other info from database and place info under element intances.instance...
SELECT   i.instance AS [Name]
       , i.edition AS [Edition]
       , i.patchlevel AS [Patchlevel]
       -- Retrieve info from database table on key instance_id place info under child element Databases...
       , ( SELECT   d.[database] AS [Name]
                  , d.RecoveryModel AS [RecoveryModel] 
                  -- Retrieve ref data from OTAP table...
                  , ( SELECT e.OTAP FROM OTAP e WHERE e.ID_OTAP = d.ID_OTAP ) AS [OTAP]
                  -- Retrieve info from application to which this database belongs...
                  , ( SELECT   a.Applicatienaam AS [Name]
                             , a.AuthenticatieModus As [AuthType]
                      FROM applicatie a WHERE a.ID_applicatie = d.ID_applicatie 
                      FOR XML PATH ('Application'), Type )
           FROM [database] d 
           WHERE d.instance_id = i.instance_id 
           FOR XML PATH ('Database'), Type 
           ) AS [Databases]
FROM [dbo].[instances] i FOR XML PATH('Instance'), root('Instances'), Elements

XML Output:

<instances>
  <instance>
    <name>SERVER1\INSTANCE</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.8888.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database1</name>
        <recoverymodel>SIMPLE</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application1</name>
          <authtype>SQL Server</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
  <instance>
    <name>SERVER2</name>
    <edition>Enterprise Edition</edition>
    <patchlevel>11.00.9999.00 (SPX)</patchlevel>
    <databases>
      <database>
        <name>Database2</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Production</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
      <database>
        <name>Database3</name>
        <recoverymodel>FULL</recoverymodel>
        <otap>Testing</otap>
        <applicatie>
          <name>Application2</name>
          <authtype>Windows</authtype>
        </applicatie>
      </database>
    </databases>
  </instance>
</instances><instances>
</instances>

XML Xpath Basics

/ XML /

Expressions

/rootelement/childelement //elementname
Return all elements named 'elementname' 

//elementname/childelement[1] 
Get only 1st childelement with name childelelement

. 
Current node

..
Parrent element

Functions

name()
Name of the current node

comment()
Display comments

text()
Display CDATA text

document('/path/to/file.xml')/rootelement/childelement 
Set pointer to external file

string-length(element/childelement)
Get string length

concat(element/childelement1, ' ', element/childelement2)
Concatenate strings

contains(elemen/childelement, 'substring')
String contains substring

starts-with(), normalize-space(), substring-before(), substring-after(), count() not(), true(), false(), ...