Run SQL query against Azure SQL managed Instance using PowerShell

Question

My task is to execute Azure MI database SP using Powershell.

I am using connection string. I believe I am able to login to the SQLMI database using the connection string with PowerShell.

However, I am getting an error while running the SQL. Below is the code and exception. Could you please help me?

$managedInstanceName = "MI"

$databaseName = "DB"

$serverAdminLogin = "t1"

$serverAdminPassword = "t1"

$query = "execute dbo.SP1"

$connectionString = "Server=tcp:$managedInstanceName.database.windows.net,1433;Persist Security Info=False;UserID=$serverAdminLogin;Password=$serverAdminPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Invoke-Sqlcmd -ServerInstance $managedInstanceName -Database $databaseName -Query "execute dbo.SP1"

Error:'Invoke-Sqlcmd' is not recognized as the name of a cmdlet.

Answer 1

Error:'Invoke-Sqlcmd' is not recognized as the name of a cmdlet.

The error message you got is that the cmdlet Invoke-Sqlcmd is not recognized. This can be the result of a module missing or a bad installation.

• Try installing the SqlServer module by executing the following PowerShell command:

Install-Module -Name SqlServer

• Also, It appears that you are not using the $connectionString variable that you previously specified in your code. Instead, you are utilising the Invoke-Sqlcmd cmdlet's -ServerInstance and -Database arguments.

Instead, you may try supplying the $connectionString variable as the value of the -ConnectionString option.

Example:

Invoke-Sqlcmd  -ConnectionString "Data Source=$managedInstanceName;User Id=$serverAdminLogin; Password =$serverAdminPassword; Integrated Security=False;" -Query "$Query"