如何使用Azure自动化运行存储过程？

I´m really new to the Azure platform and maybe this is a dumb question but i haven´t manage to find information about this topic. I really need help with this. I'm deploying a database used by a web service for a university project. In this database I have an stored procedure and need to run it daily.

我真的是Azure平台的新手，也许这是一个愚蠢的问题，但我找不到有关这个主题的信息。我真的需要帮助。我正在为大学项目部署Web服务使用的数据库。在这个数据库中，我有一个存储过程，需要每天运行它。

Found that with Azure Automation you could program or schedule this kind of actions. I "installed" the service and I´m trying to create the "runbook" but don´t know how or what to code in here because I never used PowerShell that much.

发现使用Azure自动化，您可以编程或安排此类操作。我“安装”了该服务，我正在尝试创建“Runbook”，但不知道在这里编写代码的方式或内容，因为我从未使用过PowerShell。

Any help provided will be appreciated. Thanks in advance!

任何帮助将不胜感激。提前致谢！

EDIT 1:

编辑1：

So I'm trying to use this code to make the magic:

所以我正在尝试使用这段代码来制作魔法：

 workflow WORKFLOW_NAME
{ 
    param( 

    ) 


    inlinescript { 
        # Define the connection to the SQL Database 
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=SERVER_NAME.database.windows.net;Initial Catalog=DATABASE_NAME;Integrated Security=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False") 

        # Open the SQL connection 
        $Conn.Open() 

        # Define the SQL command to run. 
        $Cmd=new-object system.Data.SqlClient.SqlCommand("exec PROCEDURE_NAME", $Conn) 
        $Cmd.CommandTimeout=120 

        # Execute the SQL command 
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) 

        # Close the SQL connection 
        $Conn.Close() 
    } 
}

The thing is that when I save the RunBook and publish it, it says there is no errors. When I run the RunBook it starts and finish sending no errors nor exceptions, so you could expect it did the work right?, but when consulting the database there is no modifications in the tables the procedure is intended to modify. What could be the error in this? what am I doing wrong?

问题在于，当我保存RunBook并发布它时，它表示没有错误。当我运行RunBook时，它开始并完成发送没有错误或异常，所以你可以期待它做的工作正确吗？但是在咨询数据库时，没有修改过程要修改的表。这可能是什么错误？我究竟做错了什么？

I took the reference code from https://gallery.technet.microsoft.com/scriptcenter/How-to-use-a-SQL-Command-be77f9d2#content , personalized it and got rid of the "param" because the RunBook when started, never asked for any entry parameters, so I decided to go with the full connection string. I'm using the same connection string as my c# project, which connects and works perfectly.

我从https://gallery.technet.microsoft.com/scriptcenter/How-to-use-a-SQL-Command-be77f9d2#content中获取了参考代码，对其进行了个性化并摆脱了“param”，因为RunBook何时开始，从未要求任何入口参数，所以我决定使用完整的连接字符串。我正在使用与我的c＃项目相同的连接字符串，它连接并完美地工作。

I'm using the "new" azure interface, don't know if this could be of any importance in this regard.

我正在使用“新”天蓝色界面，不知道这在这方面是否具有任何重要性。

Again, thank you for any help you could provide.

再次感谢您提供的任何帮助。

2 个解决方案

#1

I found the core of the problem, the code works just fine, the issue was I was using the wrong type of RunBook inside Azure Automation, so, make sure you're running a Workflow PowerShell instead of a simple PowerShell.

我找到了问题的核心，代码工作正常，问题是我在Azure自动化中使用了错误类型的RunBook，因此，请确保您运行的是Workflow PowerShell而不是简单的PowerShell。

The code I posted in the question works, but I found a better way to understand what the code made by using the example provided here: https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/ (thanks to @Joseph Idziorek)

我在问题中发布的代码有效，但我找到了一个更好的方法来理解使用此处提供的示例所做的代码：https：//azure.microsoft.com/en-us/blog/azure-automation-your- sql-agent-in-the-cloud /（感谢@Joseph Idziorek）

Here is the working code for anyone who ran into the same problem as i did:

以下是遇到与我相同问题的任何人的工作代码：

workflow NAME-OF-YOUR-WORKFLOW
{
    Write-Output "JOB START BEFORE INLINESCRIPT"

    inlinescript
    {
        Write-Output "JOB START"
        # Create connection to Master DB
        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
        $MasterDatabaseConnection.ConnectionString = "Data Source=YOUR-DATABASE-SERVER-NAME.database.windows.net;Initial Catalog=YOUR-DATABASE-NAME;Integrated Security=False;User ID=YOUR-DATABASE-USERNAME;Password=YOUR-DATABASE-PASSWORD;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
        $MasterDatabaseConnection.Open()

        Write-Output "CONNECTION OPEN"

        # Create command
        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
        $MasterDatabaseCommand.CommandText = "YOUR-PROCEDURE-NAME"

        Write-Output "DATABASE COMMAND TEXT ASSIGNED"

        # Execute the query
        $MasterDatabaseCommand.ExecuteNonQuery()

        Write-Output "EXECUTING QUERY"

        # Close connection to Master DB
        $MasterDatabaseConnection.Close() 

        Write-Output "CONNECTION CLOSED"
    }    
    Write-Output "WORK END - AFTER INLINESCRIPT"
}

The Write-outputs are optional, if you want to check what part of the code is working and if everything worked after each run.

如果要检查代码的哪个部分正在工作以及每次运行后是否一切正常，则写输出是可选的。

#2

The following article and subsequent sample code should be a good starting place to get PowerShell code executing against Azure SQL Database from Azure Automation: https://azure.microsoft.com/en-us/blog/azure-automation-your-sql-agent-in-the-cloud/

以下文章和后续示例代码应该是从Azure自动化获取针对Azure SQL数据库执行PowerShell代码的良好起点：https：//azure.microsoft.com/en-us/blog/azure-automation-your-sql-剂中的云/

#1