dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Invoke-Sqlcmd2

Runs a T-SQL script. Invoke-Sqlcmd2 runs the whole script and only captures the first selected result set, such as the output of PRINT statements when -verbose parameter is specified.
Parameterized queries are supported.

Help details below borrowed from Invoke-Sqlcmd.

Screenshots

dbatools-Invoke-SqlCmd2

Examples

Connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.

Invoke-Sqlcmd2 -ServerInstance “MyComputer\MyInstance” -Query “SELECT login_time AS ‘StartTime’ FROM sysprocesses WHERE spid = 1”

Reads a file containing T-SQL statements, runs the file, and writes the output to another file.

Invoke-Sqlcmd2 -ServerInstance “MyComputer\MyInstance” -InputFile “C:\MyFolder\tsqlscript.sql” | Out-File -filePath “C:\MyFolder\tsqlscript.rpt”

Uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
VERBOSE: hello world

Invoke-Sqlcmd2 -ServerInstance “MyComputer\MyInstance” -Query “PRINT ‘hello world'” -Verbose

Author

This command was created by Chrissy LeMaire. You can find Chrissy on Twitter.

Get-Help

From PowerShell, execute Get-Help Invoke-Sqlcmd2 -Detailed for more information on this function.

Source Code

Want to see the source code? View Invoke-Sqlcmd2.ps1 on GitHub
 

Related commands