如何使用Azure的Automation自动化来调用存储过程的SQL数据库

Azure Automation(Azure自动化)是什么？

微软 Azure 自动化提供一种方式使用户可以自动执行通常在一个云计算和企业环境中的手动、 长时间运行、 易出错，和经常重复的任务 它可以节省时间和提高常规管理任务的可靠性，甚至安排他们在固定的时间间隔自动执行。 您可以使用runbook自动化流程或使用所需状态配置自动化配置管理。

在这篇文章，我将为您介绍如何使用 azure 自动化来自动调用azure sql数据库的存储过程。

前提︰

1. 已有Azure的订阅

2. 已建立Azure SQL数据库

3. 已建立Azure Automation的帐户

步骤︰

1、 创建 Azure SQL数据库中的表

CREATE TABLE dbo.yangtestsp

(

  Id uniqueidentifier,

  datetimestamp nvarchar(127),

  city varchar(20),

  country varchar(20),

)

GO

CREATE CLUSTERED INDEX ClusteredID ON dbo.yangtestsp(Id);

GO

2、 向表中插入一些数据

INSERT INTO [yangtestsp] (id, city, country)

VALUES (newid(), 'shanghai', 'china')

INSERT INTO [yangtestsp] (id, city, country)

VALUES (newid(), ‘wuxi’, 'china')

3、 创建一个存储过程

CREATE PROCEDURE sp_yangtest @city varchar(20), @country varchar(20)

AS

BEGIN

SELECT * FROM [yangtestsp] where city=@city and country=@country

END

4、 使用 PowerShell 来调用存储过程

该脚本列出如下︰

function exec-storedprocedure($storedProcName,
   [hashtable] $parameters=@{},
   $conn,[switch]$help){
     function put-outputparameters($cmd, $outparams){
       foreach($outp in $outparams.Keys){
         $cmd.Parameters.Add("@$outp", (get-paramtype $outparams[$outp])).Direction=[System.Data.ParameterDirection]::Output
       }
     }
     function get-outputparameters($cmd,$outparams){
       foreach($p in $cmd.Parameters){
         if ($p.Direction -eq [System.Data.ParameterDirection]::Output){
           $outparams[$p.ParameterName.Replace("@","")]=$p.Value
         }
       }
     }
     function get-paramtype($typename,[switch]$help){
       switch ($typename){
        'uniqueidentifier' {[System.Data.SqlDbType]::UniqueIdentifier}
        'int' {[System.Data.SqlDbType]::Int}
        'xml' {[System.Data.SqlDbType]::Xml}
        'nvarchar' {[System.Data.SqlDbType]::NVarchar}
        default {[System.Data.SqlDbType]::Varchar}
       }
     }
    if ($help){
      Write-Host $msg
      return
    }
    $close=($conn.State -eq [System.Data.ConnectionState]'Closed')
    if ($close) {
    $conn.Open()
    }

 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandType=[System.Data.CommandType]'StoredProcedure'
 $cmd.CommandText=$storedProcName
 foreach($p in $parameters.Keys){
   $cmd.Parameters.AddWithValue("@$p",[string]$parameters[$p]).Direction=
   [System.Data.ParameterDirection]::Input
 }

put-outputparameters $cmd $outparams
 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 [Void]$da.fill($ds)
 if ($close) {
   $conn.Close()
 }
 get-outputparameters $cmd $outparams

return @{data=$ds;outputparams=$outparams}

}

$conn = "Server=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

$res=exec-storedprocedure -storedProcName 'sp_yangtest' -parameters @{city="wuxi";country="china"} $conn

#retrieve proc output from returned object

$res.data.Tables #dataset containing the datatables returned by selects

运行输出为︰

5、将此脚本放在AzureAutomation(Azure自动化)中，通过创建runbook，并设置一个适当的计划，然后你可以自动调用这个脚本。

关于如何创建runbook，你可以参考微软官方文档：《在Azure中创建我的第一个Runbook》

对于设置运行计划，你可以参考微软官方文档：《在 Azure 自动化中计划 Runbook – 创建计划》

（END）

文章源自：Dev Chat 在线支持

作者：Yang Wang