Invoke-Sqlcmd

Run a script containing statements supported by the SQL Server SQLCMD utility. .

Syntax
      Invoke-Sqlcmd [-ServerInstance PSObject] [-Database String] [-EncryptConnection]
         [-Username String] [-Password String] [[-Query] String]
            [-QueryTimeout Int32] [-ConnectionTimeout Int32] [-ErrorLevel Int32]
               [-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
                  [-AbortOnError] [-DedicatedAdministratorConnection] [-DisableVariables]
                     [-DisableCommands] [-HostName String] [-NewPassword String]
                        [-Variable String[]] [-InputFile String] [-OutputSqlErrors Boolean]
                           [-IncludeSqlUserErrors] [-SuppressProviderContextWarning] [-IgnoreProviderContext]
                              [-OutputAs OutputType] [CommonParameters]

      Invoke-Sqlcmd [[-Query] String] [-QueryTimeout Int32] [-ErrorLevel Int32]
         [-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
            [-AbortOnError] [-DisableVariables] [-DisableCommands] [-Variable String[]]
               [-InputFile String] [-OutputSqlErrors Boolean] [-IncludeSqlUserErrors]
                  [-OutputAs OutputType] -ConnectionString String [CommonParameters]

Key:
   -InputFile  Ensure all input scripts are secured with appropriate NTFS permissions.
   -OutputAs   The type of the results to get: DataSet, DataTables, DataRows.

The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility.

The -password parameter accepts only a clear-text password on the command line

The commands supported are T-SQL statements and the subset of the XQuery syntax that is supported by the database engine. This cmdlet also accepts many of the commands supported natively by SQLCMD, such as GO and QUIT.

This cmdlet also accepts the SQLCMD scripting variables, such as SQLCMDUSER.
By default, this cmdlet does not set SQLCMD scripting variables.
This cmdlet does not support the use of commands that are primarily related to interactive script editing.
The commands not supported include :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

When this cmdlet is run, the first result set that the script returns is displayed as a formatted table. If subsequent result sets contain different column lists than the first, those result sets are not displayed. If subsequent result sets after the first set have the same column list, their rows are appended to the formatted table that contains the rows that were returned by the first result set.

You can display SQL Server message output, such as those that result from the SQL PRINT statement, by specifying the -Verbose parameter.

Examples

Connect to a named instance and run a script

PS C:\> Invoke-Sqlcmd -InputFile "C:\demo\demo.sql" -ServerInstance "Computer64\Instance64"

Run a script and send the output to a file:

PS C:\> Invoke-Sqlcmd -InputFile "C:\demo\demo.sql" -ServerInstance "Computer64\Instance64"| Out-File -FilePath "C:\demo\output.rpt"

Capture data into a DataSet object

PS C:\> $DS = Invoke-Sqlcmd -ServerInstance "Computer64" -Query "SELECT ID, Item FROM MyDB.dbo.MyTable" -As DataSet
PS C:\> $DS.Tables[0].Rows | %{ echo "{ $($_['ID']), $($_['Item']) }" }
{ 10, AAA }
{ 20, BBB }
{ 30, CCC }

"Always do everything you ask of those you command” ~ George S. Patton

Related commands

A condemnation of Invoke-SqlCmd by Steven Swenson
SQLCMD - Run a T-SQL statement, procedure or script.