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
(3)
7,482 times
Add to favorites
Windows Azure
12/15/2016
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • 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?
      
      Cheers
      
      Rich
  • 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) 
              [void]$Da.fill($Ds) 
       
              # Output the count 
              $Ds.Tables.Column1 
      
      Thank you for any help you could provide.
      
      Hope you have a great day!
    • function Exec-Sproc{
      	param($Conn, $Sproc, $Parameters=@{})
       
      try{
      
              $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)
      	    $SqlCmd.Connection.Close()
      	    return $DataSet.Tables[0]
      
      }
      catch{
          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. 
      
      Thanks,
      Ashish
  • 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: http://msdn.microsoft.com/en-us/library/jj585422.aspx. 
      
      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: http://msdn.microsoft.com/en-us/library/dn715779.aspx. 
      
      Azure SQL Database is automatically backed up described by the policies outlined in the Azure SQL Database Business Continuity (http://msdn.microsoft.com/en-us/library/hh852669.aspx). 
      
      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: http://msdn.microsoft.com/en-us/library/ff951631.aspx#CopyPSaspx