Azure Automation Powersell Runbook fails: 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet

Question

I have done all the steps of the following tutorial to have a runbook for automating index and statistic maintenance:

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/01/15/automating-azure-sql-db-index-and-statistic-maintenance-using-azure-automation/

I was able to complete all the tutorial without errors, but when I execute the runbook, it returns me an error:

Invoke-Sqlcmd : The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:6 char:16
+ $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Use ...
+ ~~~~~~~~~~~~~
+ CategoryInfo: ObjectNotFound: (Invoke-Sqlcmd:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

What can I do? I use SQL Server with Azure. Specifically, Azure SQL Database with pricing/model tier "S4 Standard (200 DTUs)".

Answer 1

Follow that blog, I reproduced your error.

You can deploy sqlserver module via this page:

Here is my runbook:

$AzureSQLServerName = "jasonsql"
$AzureSQLDatabaseName = "jasondatabase"

$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "SQLLogin"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "SELECT * FROM INFORMATION_SCHEMA.TABLES " -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1

Write-Output $SQLOutput

Here is the result:

Hope this helps.