PowerShell provides the Invoke-Sqlcm command in order to connect run and SQL queries in SQL database servers. We can use the Invoke-Sqlcmd command in a different way by providing credentials or providing a connection string. Also, we can run SQL queries directly or provide the SQL using an SQL file.
Run SQL Command
We can use the Invoke-Sqlcmd command like below to run SQL command or SQL query. The SQL query is specified with the -Query option below. The database server is specified with the -ServerInstance .
PS> Invoke-Sqlcmd -Query "SELECT * FROM Users" -ServerInstance "dbserver1\MainInstance"Run SQL Script File
The Invoke-Sqlcmd command can be used to run an SQL script file. The SQL script file contains SQL commands to run on the specified SQL database server. The SQL script file is specified with the -InputFile parameter.
PS> Invoke-Sqlcmd -InputFile "C:\Users\ismail\Test.sql" -ServerInstance "dbserver1\MainInstance"Connect SQL Server Using ConnectionString
Most programming languages used ConnectionString in order to specify connections to the database servers. The Connection String contains the remote database server IP address or hostname, username, password, configuration, etc. We can use the -ConnectionString in order to specify a connection string to connect remote database server.
PS> Invoke-Sqlcmd -ConnectionString "Data Source=db1;Initial Catalog=MyDatabase;Integrated Security=True" -Query "SELECT * FROM Users"