The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility.
The commands supported are Transact-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
Example 1: Connect to a named instance and run a script
This command connects to a named instance of the SQL Database Engine on a computer and runs a basic Transact-SQL script.
Example 2: Invoke commands in a script file and save the output in a text file
PS C:\> Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"
Output sent to TestSqlCmd.rpt.
This command reads a file containing Transact-SQL statements and SQLCMD commands, runs the file, and writes the output to another file.
The output file may contain proprietary information, so you should secure the output files with the appropriate NTFS permissions.
Example 3: Invoke a script and pass in variable values from a string
This command uses an array of character strings as input to the Variable parameter.
The array defines multiple SQLCMD variables.
The $ signs in the SELECT statement that identify the SQLCMD variables are escaped using the back-tick (`) character.
Example 4: Invoke a script and pass in variables from the SQL database engine
This command uses Set-Location to navigate to the SQL Server Windows PowerShell provider path for an instance of the SQL Database Engine.
Then it calls Get-Item to retrieve a SQL Management Object Server object for use as the ServerInstance parameter of Invoke-Sqlcmd.
This command uses the Windows PowerShell Verbose parameter to return the message output of the SQL PRINT command.
Example 6: Invoke a command using a positional string as input
PS C:\> Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName;"
WARNING: Using provider context. Server = MyComputer, Database = AdventureWorks2014.
DatabaseName
------------
AdventureWorks2014
This command uses a positional string to supply the input to the Query parameter.
It also demonstrates how Invoke-Sqlcmd uses the current path to set the database context to AdventureWorks2014.