CODEWITHSUNDEEP
powershellazureazure-automation
martial
martial

Reputation: 3883

Azure Automation error

I have the following PowerShell script in Azure Automation. All it does is running a stored procedure called 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")

}

But when I do a test run, I get the following error:

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

what is the problem?

edit

With the help of jisaak, I have this final working version:

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()  
    }
}

I set the CommandTimeout since when I debug it throws an exception saying:

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.)

Upvotes: 1

Views: 279

Answers (1)

Martin Brandl
Martin Brandl

Reputation: 58931

The error points it out, Microsoft.SqlServer.Management.Common.ServerConnection is not available. Use System.Data.SqlClient.SqlConnection instead:

$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()

Im not sure, but you maybe have to wrap the script in a inlinescript.

Upvotes: 1

Related Questions