Azure Automation Powershell

Question

I trying to automate to retrieve data from azure SQL database from azure automation run book power shell. I found that the SQL Server module was missing in the modules of azure automation account. I have imported that module. But still that command is not working.

SQL server module has been imported to azure automation run book . I've attached the modules image below.

But when I run the command "Invoke-Sqlcmd" from the azure automation run book test pane it throws the below error.

Answer 1

Invoke-Sqlcmd was not working in azure automation runbook powershell.

So I used to powershell workflow to execute that query. It executed successfully. The below command is a powershell work flow runbook which connects to a database and executes the query.

workflow "runbookValue"
{
inlinescript
{
    $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
    $MasterDatabaseConnection.ConnectionString = "ConnectionStringValue"

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

    # Create command
    $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
    $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
    $MasterDatabaseCommand.CommandText = "<execute the query>"

    # Execute the query
    $MasterDatabaseCommand.ExecuteNonQuery()

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

Answer 2

If I import default SqlServer version(21.0.17224) from Modules gallery, I also can reproduce the issue you mentioned.

The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet

Please have a try to use the SqlServer 21.0.17199, it works correctly on my side.