PowerShell query SQL database
Invoke-Sqlcmd is a SQL Server cmdlet that runs scripts that CONTAIN statements from the languages ( Transact-SQL and XQuery) and commands supported by the sqlcmd utility.
Using Invoke-Sqlcmd
The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a Windows PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.
This is an example of calling Invoke-Sqlcmd to execute a simple query, similar to specifying sqlcmd with the -Q and -S options:
Invoke-Sqlcmd -Query "SELECT GETDATE AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
This is an example of calling Invoke-Sqlcmd, specifying an input file and piping the output to a file This is similar to specifying sqlcmd with the -i and -o options:
Invoke-Sqlcmd -InputFile "C:\MyFolder\TestSQLCmd.sql" | Out-File -filePath "C:\MyFolder\TestSQLCmd.rpt"
This is an example of using a Windows PowerShell array to pass multiple sqlcmd scripting variables to Invoke-Sqlcmd. The "$" characters identifying the sqlcmd scripting variables in the SELECT statement have been escaped by using the PowerShell back-tick "`" escape character:
$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'" Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray
This is an example of using the SQL Server provider for Windows PowerShell to navigate to an instance of the Database Engine, and then using the Windows PowerShell Get-Item cmdlet to retrieve the SMO Server object for the instance and passing it to Invoke-Sqlcmd:
Set-Location SQLSERVER:\SQL\MyComputer\MyInstance Invoke-Sqlcmd -Query "SELECT GETDATE AS TimeOfQuery;" -ServerInstance (Get-Item .)
The -Query parameter is positional and does not have to be named. If the first string that is passed to Invoke-Sqlcmd: is unnamed, it is treated as the -Query parameter.
Invoke-Sqlcmd "SELECT GETDATE AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
Path Context in Invoke-Sqlcmd
If you do not use the -Database parameter, the database context for Invoke-Sqlcmd is set by the path that is active when the cmdlet is called.