Problems testing a Runbook from "Automation Accounts"

Question

Hello.

I created a powershell runbook that's supposed to execute a procedure stored in my SQL Database (also located in Azure).

I'm using the following code for that:

Write-Output "Run started"

# Instantiate the connection to the SQL Database

$sqlConnection = new-object System.Data.SqlClient.SqlConnection


$sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"


$sqlConnection.Open()


Write-Output "Azure SQL database connection opened"


# Define the SQL command to run

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

$sqlCommand.CommandTimeout = 120

$sqlCommand.Connection = $sqlConnection


Write-Output "Issuing command to run stored procedure"


# Execute the SQL command

$sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'

$result = $sqlCommand.ExecuteNonQuery()


Write-Output "Stored procedure execution completed"


# Close the SQL connection

$sqlConnection.Close()


Write-Output "Run completed"

(Taken from this tutorial: https://global.hitachi-solutions.com/blog/azure-sql-databases-stored-procedure/)

However, when I click on the "Test pane" and start a test, it stays on the following screen for hours and never actually executes the code.

I am sure I'm using the right credentials for the server, since I tested them in other environments, so the problem must be elsewhere.

Does anyone please have any clue on how I could get to test this?

Thanks in advance

Answer 1

Thanks for the answer!

I managed to solve the problem on my own. When creating the runbook, I originally chose the 7.2 version. I created a new one using the 5.1 version and everything worked correctly

Answer 2

@Sinais I see that you are trying to test runbook using the "Test Pane" available in Azure Automation and it does not seem to work.

Based on the symptoms, the issue does not seem to be with the script itself. Note that even the output from first line (Write-Output "Run started") has not been streamed to this pane. I tested a few minutes back and it seems to be working as expected for me. Here are some steps that you could take to troubleshoot/isolate it further

1. Verify if the Stored procedure ran in the Database from logs - this would help understand if the runbook executed and only the output stream was missing
2. Refresh portal and go to this runbook --> "Test Pane". Click "View last test". If somehow browser did not show this data for the job executed in backend, you should be able to get this information from last run.
3. Clearing up browser cache/disabling extension and trying it in private session could also be tested to see if that works. If this does not work, I would suggest reaching out to Azure Support to have this investigated 1:1 - which would require backend logs/traces to be collected.

Please let me know if it helps.