6

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)".

Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
RuSSe
  • 173
  • 1
  • 7

1 Answers1

10

Follow that blog, I reproduced your error.

You can deploy sqlserver module via this page:

enter image description here

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:

enter image description here

Hope this helps.

Jason Ye
  • 13,710
  • 2
  • 16
  • 25
  • Yes! That fixes it. Thank you very much! – RuSSe Feb 27 '18 at 06:56
  • 1
    To anyone else running into this, there is a problem with more recent versions of this module. I found other people trying to do azure automation ran into the same issue and were ok after downgrading to 21.0.17199 You can deploy right from the page Jason listed or below: https://www.powershellgallery.com/packages/SqlServer/21.0.17199 – Echostorm Mar 05 '18 at 15:35
  • I’ve asked relevant question [should-i-use-powershell-subexpression-when-invoke-sqlcmd-in-azure-runbook](https://stackoverflow.com/questions/67453109/should-i-use-powershell-subexpression-when-invoke-sqlcmd-in-azure-runbook) or call Invoke-Sqlcmd directly – Michael Freidgeim May 08 '21 at 23:59