powershell - Azure 自动化错误

Question

我在 Azure 自动化中有以下 PowerShell 脚本。它所做的只是运行一个名为 AddWeeks 的存储过程。

workflow GenerateWeeks
{
    $serverInstance="[my_db_server]" 
    $userName="[my_username]"
    $password="[my_password]"

    [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') |out-null
    $ServerConnection =new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverInstance,$userName, $password
    $ServerConnection.ExecuteNonQuery("declare @date date set @date=getdate()   exec [xxxx].dbo.AddWeeks 300,@date")    

}

但是当我进行测试运行时，出现以下错误:

Runbook definition is invalid. Could not find type Microsoft.SqlServer.Management.Common.ServerConnection. Load the type(s) and try again. 

问题是什么？

编辑

在 jisaak 的帮助下，我有了这个最终的工作版本:

workflow GenerateWeeks
{
    InlineScript 
    {
        $con = New-Object System.Data.SqlClient.SqlConnection
        $con.ConnectionString = "Server = xxx; Database=xxx; User ID = xxx; Password = xxx;"
        $con.Open();
        $sql = "declare @date date set @date=getdate()   exec [xxx].dbo.AddWeeks 300,@date"
        $cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$con)
        $cmd.CommandTimeout=300
        $cmd.ExecuteNonQuery()  
    }
}

我设置了 CommandTimeout，因为当我调试时它会抛出异常:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Answer 1

错误指出，Microsoft.SqlServer.Management.Common.ServerConnection 不可用。使用System.Data.SqlClient.SqlConnection相反:

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server = $serverInstance; User ID = $userName; Password = $password;"
$con.Open();
$cmd = $con.CreateCommand("declare @date date set @date=getdate()   exec [xxxx].dbo.AddWeeks 300,@date")
$cmd.ExecuteNonQuery()

我不确定，但您可能必须将脚本包装在内联脚本中。