Invoke-SqlCmd on Azure Runbook

Question

I'm trying to use the Invoke-SqlCmd cmdlet from a version 7.2 runbook to invoke a stored procedure. After installing the SqlServer module, and doing:

Import-Module sqlserver
Invoke-SqlCmd -ServerInstance $serverName -Database 'data' -Query 'exec MyStoredProc'

I get the error:

This module requires PowerShell 7.2.1+. Please, upgrade your PowerShell version by checking https://aka.ms/pscore6. System.Management.Automation.CommandNotFoundException: The term 'Invoke-SqlCmd' is not recognized as a name of a cmdlet, function, script file, or executable program.

From what I can see, the max supported version of runbooks is 7.2. Am I missing something

Answer 1

I also faced similar error to resolve this follow below steps:

• You can deploy sqlserver module via this page

• Create runbook with PowerShell version 5.1

• My sample code:

$SQL_Server_Name = Get-AutomationVariable -Name "servername"
$SQL_DB_Name = Get-AutomationVariable -Name "dbname"

$Query = "execute sp1"

invoke-sqlcmd -ServerInstance "$SQL_Server_Name" -Database "$SQL_DB_Name" -Username "username" -password "password" -Query "$Query" -Encrypt

Output: