How to use a SQL Command in an Azure Automation Runbook

This PowerShell Workflow runbook outputs the number of records specified in a SQL Server database table.

4.7 Star
17,103 times
Add to favorites
Windows Azure
E-mail Twitter Digg Facebook
  • Child Runbook to return iterable set
    1 Posts | Last post March 01, 2018
    • Hi, 
      Thanks for the example - What I don't understand is how you can apply this logic in a repeatable way. For example if I was writing an application in C# I would have a class with a method in that returned say - a datatable for a query. I could then run any query and get a datatable reusing that same code.
      Is this possible via a runbook - my understanding is that a function has to basically be another runbook and that the only value that you can obtain back from a child runbook is effectively a string (not too sure how you could iterate though that). 
      what I want to have is a child runbook I can call from parent runbooks which passes back to the parents iterables sets. 
      Is this possible?
  • Capturing the "print" output
    1 Posts | Last post September 04, 2017
    • Hi,
       Thanks for the article. Any idea how to direct the "print" or "raiserror" messages to the output so it can be seen in the logs?
      I've tried putting a SqlInfoMessageEventHandler on the connection, but it doesn't seem to fire. I tried it with a dataadapter, and with ExecureNonQuery.. no joy either way.
      Any ideas?
  • Executing a procedure
    2 Posts | Last post January 16, 2017
    • Hello!
      Wanted to thank you for posting this excellent example. I was wondering if i could personalize it to make it run a stored procedure. I was thinking on changing the query description to "exec PROCEDURE_NAME".
      I have doubts about doing this because i don´t understand when it executes the command. Since i wont need a table to consult I could the $table parameter right?
      Im not sure about what to do with this code in this case, because the Procedure i want to call/use wont return anything:  
              # Execute the SQL command 
              $Ds=New-Object system.Data.DataSet 
              $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) 
              # Output the count 
      Thank you for any help you could provide.
      Hope you have a great day!
    • function Exec-Sproc{
      	param($Conn, $Sproc, $Parameters=@{})
              $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
      	    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
      	    $SqlCmd.Connection = $Conn
              $SqlCmd.CommandTimeout = 0
      	    $SqlCmd.CommandText = $Sproc
      	    foreach($p in $Parameters.Keys)
       		    [Void] $SqlCmd.Parameters.AddWithValue("@$p",$Parameters[$p])
      	    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)
      	    $DataSet = New-Object System.Data.DataSet
      	    [Void] $SqlAdapter.Fill($DataSet)
      	    return $DataSet.Tables[0]
          Write-Output "Error While executing stored procedure $($_.Exception.ToString())"
          return $null
      Call the function like this:
      Exec-Sproc -Conn $ConnectionString -Sproc "Stored Proc Name" -Parameters @{}
  • How to allow connections from Azure services
    1 Posts | Last post July 13, 2016
    • I am trying to use this runbook in my environment where I have SQL Server installed on an Azure VM, and would like to run DB management scripts using automation runbooks. Could you elaborate on how to setup SQL Server to allow incoming connections from Azure services?
  • How to pass SqlCredentials
    1 Posts | Last post April 29, 2016
    • Hi, 
      I am using this template. I want to know how to pass SqlCredentials to runbook. 
  • How Create AzureSqlDatabase RunBook with automation ?
    2 Posts | Last post July 29, 2014
    • I want create Runbook where i have to write command for  Delete Database, Copy database, BackUp database, RollBack Delete database, Copy database to Another DataBase . how can i do that please help me any one ?
    • Hi, you can use the PS commandlets to create or delete Azure SQL Database using the following Azure SQL DB management commandlet New-AzureSqlDatabase and Remove-AzureSqlDatabase
      More options are described in the following topic: 
      You can restore a Azure SQL DB using the following restore commandlet, here is an example: PS C:\>$operation = Start-AzureSqlDatabaseRestore –SourceServerName "a234b56" –SourceDatabaseName "mydatabase" –TargetDatabaseName "mydatabaserestored" –PointInTime "2013-01-01 06:00:00"
      and more information is available in the following topic: 
      Azure SQL Database is automatically backed up described by the policies outlined in the Azure SQL Database Business Continuity ( 
      You can copy using different methods which are also including PS, an example PS C:\>Start-AzureSqlDatabaseCopy -ServerName "abc" -DatabaseName "Orders" -PartnerDatabase "Orders Copy"
      More option are described in the following topic: