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.

score 1 · Answer 1 · answered Mar 26 '18 at 06:46

1

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.

answered Mar 26 '18 at 06:46

Tom Sun - MSFT

24,161
3
30
47

I want to install this module from azure automation runbook powershell itself. Is there a way for it? – suhas paramesh Mar 26 '18 at 06:48
If you click the button, it will redirect to azure portal then you could install it to automation account – Tom Sun - MSFT Mar 26 '18 at 06:48
Nop that's not the way I want to do it. I want to do it from powershell command. It's my requirement bud – suhas paramesh Mar 26 '18 at 06:50
Would you mind sharing why you don't want to do that? It is equals to import it from Modules gallery. After import it to the azure automation, then you could use the command `Invoke-Sqlcmd` directly from runbook script. – Tom Sun - MSFT Mar 26 '18 at 06:56
**Import-AzureRMAutomationRunbook -Name $runBookName -Path $scriptPath -ResourceGroupName $resourceGroupName -AutomationAccountName $automationAccountName -Type PowerShell** So I want user to just import the runbook by using this command and I have scheduled this runbook to run one time per day. I don't want user to go this site and click deploy to his automation account for downloading the modules. It should be the part of the runbook itself. – suhas paramesh Mar 26 '18 at 07:02
By default, `Invoke-Sqlcmd` is not supported in the Azure automation account, If we need to use the command, then we could import it from Modules gallery, then it could be used in the any script under this **automation account**, **no need** to install it for each run script. The way I mentioned is another way to install the specific version Modules from gallery. – Tom Sun - MSFT Mar 26 '18 at 07:16
Thanks bud :). I found a way to do it with powershell workflow.Cheers :) – suhas paramesh Mar 26 '18 at 07:26
Glad to hear that. If you figure it out with another way, you also could add it as an answer that will help more communites who has the same issue. – Tom Sun - MSFT Mar 26 '18 at 07:28

score 1 · Accepted Answer · answered Mar 29 '18 at 09:52

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

Azure Automation Powershell

2 Answers2