The Invoke-Sqlcmd cmdlet can be used to execute SQL statements, such as select, insert, update, and delete.
In order to use Invoke-Sqlcmd, you will first need to import the sqlps module. If PowerShell is being used on a PC that does not have SQL Server installed, the following software will need to be installed on the PC. If PowerShell is being used on the same PC as the SQL Server database, the following software should already be installed on the PC.
These files can be downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=43339.
- Microsoft System CLR Types for Microsoft SQL Server 2012 (SQLSysClrTypes.msi)
- Microsoft SQL Server 2012 Shared Management Objects (SharedManagementObjects.msi)
- Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012 (PowerShellTools.msi)
Run this command.
If problems occur with this command, you can install SQL Server Management Studio on the PC.
Interacting with a SQL Server database
Following is an example PowerShell command to select data from SQL. You can use nearly any SQL statement in PowerShell.
Invoke-Sqlcmd -Query "select * from table_name" -ServerInstance "server_name or IP address" -Database "database_name" -Username "SQL_username" -Password "SQL_password"