Invoke-SQLCMD

Invoke-Sqlcmd is a PowerShell cmdlet used to execute Transact-SQL (T-SQL) commands or scripts against SQL Server instances. This cmdlet can be used to perform various tasks, such as querying databases, creating tables, or executing stored procedures.

To use Invoke-Sqlcmd, you need to have the SQL Server PowerShell module installed on your computer. This module can be installed from the PowerShell Gallery or from the SQL Server installation media.

Common error

Invoke-sqlcmd : The term ‘Invoke-sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable

Solution

Install the Microsoft SQL Server 2016 Feature Pack’s PowerShell Extensions for SQL Server. You’ll be able to run 'Invoke-sqlcmd' in Powershell after it’s installed.

Download MSI packages

Download below installation files from Microsoft® SQL Server® 2016 Service Pack 3 Feature Pack site.

• Microsoft® System CLR Types for Microsoft SQL Server® 2016 (SQLSysClrTypes.msi)
• Microsoft® SQL Server® 2016 Shared Management Objects (SharedManagementObjects.msi)
• Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016 (PowerShellTools.msi)

Install them following below order

1. SQLSysClrTypes.msi
2. SharedManagementObjects.msi
3. PowerShellTools.msi

Reboot or downtimes are not required.

Video Recording

Below video recording demonstrates the same procedure. You can watch it if you want see more details.

Validation

After installation open a new PS window and that should be working. You can try below syntax to query any SQL instance.

Invoke-sqlcmd -ServerInstance “Server\Instance” -Query “SELECT GETDATE() AS TimeOfQuery” TimeOfQuery ———–

7/5/2022 6:51:57 PM

It’s a solution for the error 'Invoke-sqlcmd' is not recognized as a cmdlet, function, script file, or operable.’ Now that it’s working, we can get back to work.

Next steps

Once the module is installed, you can use Invoke-Sqlcmd by opening a PowerShell console and running the cmdlet with the appropriate parameters. For example, to execute a T-SQL script against a SQL Server instance, you can use the following command:

Invoke-Sqlcmd -ServerInstance “ServerName” -Database “DatabaseName” -InputFile “C:\Scripts\Script.sql”

This will connect to the specified SQL Server instance, use the specified database, and execute the T-SQL commands in the specified script file.

Overall, Invoke-Sqlcmd is a useful tool for managing SQL Server databases and automating database-related tasks using PowerShell scripts.

For any questions or suggestions please let me know in the comments.