none
Using SQL Command in Azure Automation Runbook RRS feed

  • General discussion

  • Hello guys

    Please pardon  me. I am very new in Azure and automation. I and try to read a script by SC Automation Product Team. It looks like its for experienced users. Can someone take me step by step. Where do I put my parameters and how do I know if it is working correctly. How do I see the output. Thanks here is the script below.

    <#
    .SYNOPSIS
    Outputs the number of records in the specified SQL Server database table.
    .DESCRIPTION
        This runbook demonstrates how to communicate with a SQL Server. Specifically, this runbook
    outputs the number of records in the specified SQL Server database table.
    In order for this runbook to work, the SQL Server must be accessible from the runbook worker
    running this runbook. Make sure the SQL Server allows incoming connections from Azure services
        by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure.
    This runbook also requires an Automation Credential asset be created before the runbook is
    run, which stores the username and password of an account with access to the SQL Server.
    That credential should be referenced for the SqlCredential parameter of this runbook.
    .PARAMETER SqlServer
    String name of the SQL Server to connect to
    .PARAMETER SqlServerPort
    Integer port to connect to the SQL Server on
    .PARAMETER Database
    String name of the SQL Server database to connect to
    .PARAMETER Table
    String name of the database table to output the number of records of
    .PARAMETER SqlCredential
    PSCredential containing a username and password with access to the SQL Server
    Use-SqlCommandSample -SqlServer "mtlusaveeasi.database.windows.net" -SqlServerPort 1433 -Database "Savings" -Table "Users" - SqlCredential $SomeSqlCred
    #>
    workflow Use-SqlCommandSample
    {
    param(
    [parameter(Mandatory=$True)]
    [string] $SqlServer,
    [parameter(Mandatory=$False)]
    [int] $SqlServerPort = 1433,
    [parameter(Mandatory=$True)]
    [string] $Database,
    [parameter(Mandatory=$True)]
    [string] $Table,
    [parameter(Mandatory=$True)]
    [PSCredential] $SqlCredential
    )
    # Get the username and password from the SQL Credential
    $SqlUsername = $SqlCredential.UserName
    $SqlPass = $SqlCredential.GetNetworkCredential().Password
    inlinescript {
    # Define the connection to the SQL Database
    $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
    # Open the SQL connection
    $Conn.Open()
    # Define the SQL command to run. In this case we are getting the number of rows in the table
    $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT COUNT(*) from dbo.$using:Table", $Conn)
    $Cmd.CommandTimeout=120
    # Execute the SQL command
    $Ds=New-Object system.Data.DataSet
    $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
    [void]$Da.fill($Ds)
    # Output the count
    $Ds.Tables.Column1
    # Close the SQL connection
    $Conn.Close()
    }
    }

    • Changed type Bill_Stewart Tuesday, December 5, 2017 3:42 PM
    • Moved by Bill_Stewart Tuesday, December 5, 2017 3:42 PM This is not "teach me basics about how to use a command line on a computer" forum
    Friday, November 3, 2017 2:27 PM

All replies

  • If you are not familiar with PowerShell, you need to start with the basics. A forum is about the most inefficient way to learn any new technology. There are learning links at the top of this forum.

    -- Bill Stewart [Bill_Stewart]

    Friday, November 3, 2017 2:33 PM
  • Its actually a function which accepts the parameter as inputs that has been defined inside the workflow function

    The sample usages already given in the synopsis of your code(marked in green)

    Use-SqlCommandSample -SqlServer "mtlusaveeasi.database.windows.net" -SqlServerPort 1433 -Database "Savings"

    -Table "Users" -SqlCredential $SomeSqlCred


    Please go through this link to know the basisc of workflow.

    workflow basics


    Naveen Basati

    Saturday, November 4, 2017 6:53 AM