| Author | Friedrich Weinmann (@FredWeinmann) |
| Availability | Windows, Linux, macOS |
Aliases : ivq
Want to see the source code for this command? Check out Invoke-DbaQuery on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaQuery.
A command to run explicit T-SQL commands or files.
This function is a wrapper command around Invoke-DbaAsync, which in turn is based on Invoke-SqlCmd2.
It was designed to be more convenient to use in a pipeline and to behave in a way consistent with the rest of our functions.
Invoke-DbaQuery
[[-SqlInstance] <DbaInstanceParameter[]>]
[-SqlCredential <PSCredential>]
[-Database <String>]
-Query <String>
[-QueryTimeout <Int32>]
[-As <String>]
[-SqlParameter <PSObject[]>]
[-CommandType {Text | StoredProcedure | TableDirect}]
[-AppendServerInstance]
[-MessagesToOutput]
[-InputObject <Database[]>]
[-ReadOnly]
[-NoExec]
[-AppendConnectionString <String>]
[-EnableException]
[<CommonParameters>]
Invoke-DbaQuery
[[-SqlInstance] <DbaInstanceParameter[]>]
[-SqlCredential <PSCredential>]
[-Database <String>]
[-QueryTimeout <Int32>]
-SqlObject <SqlSmoObject[]>
[-As <String>]
[-SqlParameter <PSObject[]>]
[-CommandType {Text | StoredProcedure | TableDirect}]
[-AppendServerInstance]
[-MessagesToOutput]
[-InputObject <Database[]>]
[-ReadOnly]
[-NoExec]
[-AppendConnectionString <String>]
[-EnableException]
[<CommonParameters>]
Invoke-DbaQuery
[[-SqlInstance] <DbaInstanceParameter[]>]
[-SqlCredential <PSCredential>]
[-Database <String>]
[-QueryTimeout <Int32>]
-File <Object[]>
[-As <String>]
[-SqlParameter <PSObject[]>]
[-CommandType {Text | StoredProcedure | TableDirect}]
[-AppendServerInstance]
[-MessagesToOutput]
[-InputObject <Database[]>]
[-ReadOnly]
[-NoExec]
[-AppendConnectionString <String>]
[-EnableException]
[<CommonParameters>]
PS C:\> Invoke-DbaQuery -SqlInstance server\instance -Query 'SELECT foo FROM bar'
Runs the sql query 'SELECT foo FROM bar' against the instance 'server\instance'
PS C:\> Get-DbaRegServer -SqlInstance [SERVERNAME] -Group [GROUPNAME] | Invoke-DbaQuery -Query 'SELECT foo FROM bar'
Runs the sql query 'SELECT foo FROM bar' against all instances in the group [GROUPNAME] on the CMS [SERVERNAME]
PS C:\> "server1", "server1\nordwind", "server2" | Invoke-DbaQuery -File "C:\scripts\sql\rebuild.sql"
Runs the sql commands stored in rebuild.sql against the instances "server1", "server1\nordwind" and "server2"
PS C:\> Get-DbaDatabase -SqlInstance "server1", "server1\nordwind", "server2" | Invoke-DbaQuery -File "C:\scripts\sql\rebuild.sql"
Runs the sql commands stored in rebuild.sql against all accessible databases of the instances "server1", "server1\nordwind" and "server2"
PS C:\> Invoke-DbaQuery -SqlInstance . -Query 'SELECT * FROM users WHERE Givenname = @name' -SqlParameter @{ Name = "Maria" }
Executes a simple query against the users table using SQL Parameters.
This avoids accidental SQL Injection and is the safest way to execute queries with dynamic content.
Keep in mind the limitations inherent in parameters - it is quite impossible to use them for content references.
While it is possible to parameterize a where condition, it is impossible to use this to select which columns to select.
The inserted text will always be treated as string content, and not as a reference to any SQL entity (such as columns, tables or databases).
PS C:\> Invoke-DbaQuery -SqlInstance aglistener1 -ReadOnly -Query "select something from readonlydb.dbo.atable"
Executes a query with ReadOnly application intent on aglistener1.
PS C:\> Invoke-DbaQuery -SqlInstance server1 -Database tempdb -Query Example_SP -SqlParameter @{ Name = "Maria" } -CommandType StoredProcedure
Executes a stored procedure Example_SP using SQL Parameters
PS C:\> $queryParameters = @{
>> StartDate = $startdate
>> EndDate = $enddate
>> }
PS C:\> Invoke-DbaQuery -SqlInstance server1 -Database tempdb -Query Example_SP -SqlParameter $queryParameters -CommandType StoredProcedure
Executes a stored procedure Example_SP using multiple SQL Parameters
PS C:\> $inparam = @()
PS C:\> $inparam += [PSCustomObject]@{
>> somestring = 'string1'
>> somedate = '2021-07-15T01:02:00'
>> }
PS C:\> $inparam += [PSCustomObject]@{
>> somestring = 'string2'
>> somedate = '2021-07-15T02:03:00'
>> }
>> $inparamAsDataTable = ConvertTo-DbaDataTable -InputObject $inparam
PS C:\> $inparamAsSQLParameter = New-DbaSqlParameter -SqlDbType structured -Value $inparamAsDataTable -TypeName 'dbatools_tabletype'
PS C:\> Invoke-DbaQuery -SqlInstance localhost -Database master -CommandType StoredProcedure -Query my_proc -SqlParameter $inparamAsSQLParameter
Creates an TVP input parameter and uses it to invoke a stored procedure.
PS C:\> $output = New-DbaSqlParameter -ParameterName json_result -SqlDbType NVarChar -Size -1 -Direction Output
PS C:\> Invoke-DbaQuery -SqlInstance localhost -Database master -CommandType StoredProcedure -Query my_proc -SqlParameter $output
PS C:\> $output.Value
Creates an output parameter and uses it to invoke a stored procedure.
PS C:\> $server = Connect-DbaInstance -SqlInstance localhost -Database master -AlwaysEncrypted
PS C:\> $inputparamSSN = New-DbaSqlParameter -Direction Input -ParameterName "@SSN" -DbType AnsiStringFixedLength -Size 11 -SqlValue "444-44-4444" -ForceColumnEncryption
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query 'SELECT * FROM bar WHERE SSN_col = @SSN' -SqlParameter @inputparamSSN
Creates an input parameter using Always Encrypted
PS C:\> $server = Connect-DbaInstance -SqlInstance AG1 -Database dbatools -MultiSubnetFailover -ConnectTimeout 60
PS C:\> Invoke-DbaQuery -SqlInstance $server -Query 'SELECT foo FROM bar'
Reuses Connect-DbaInstance, leveraging advanced paramenters, to adhere to official guidelines to target FCI or AG listeners.
See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover
PS C:\> Invoke-DbaQuery -SqlInstance AG1 -Query 'SELECT foo FROM bar' -AppendConnectionString 'MultiSubnetFailover=true;Connect Timeout=60'
Leverages your own parameters, giving you full power, mimicking Connect-DbaInstance's -MultiSubnetFailover -ConnectTimeout 60, to adhere to official guidelines to target FCI or AG listeners.
See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover
Specifies one or more queries to be run. The queries can be Transact-SQL, XQuery statements, or sqlcmd commands. Multiple queries in a single batch may be separated by a semicolon or a GO
Escape any double quotation marks included in the string.
Consider using bracketed identifiers such as [MyTable] instead of quoted identifiers such as "MyTable".
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value |
Specifies the path to one or several files to be used as the query input.
| Alias | InputFile |
| Required | True |
| Pipeline | false |
| Default Value |
Specify one or more SQL objects. Those will be converted to script and their scripts run on the target system(s).
| Alias | |
| Required | True |
| Pipeline | false |
| Default Value |
The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Credential object used to connect to the SQL Server Instance as a different user. This can be a Windows or SQL Server account. Windows users are determined by the existence of a backslash, so if you
are intending to use an alternative Windows connection instead of a SQL login, ensure it contains a backslash.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The database to select before running the query. This list is auto-populated from the server.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the number of seconds before the queries time out.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Specifies output type. Valid options for this parameter are 'DataSet', 'DataTable', 'DataRow', 'PSObject', 'PSObjectArray', and 'SingleValue'.
PSObject and PSObjectArray output introduces overhead but adds flexibility for working with results: https://forums.powershell.org/t/dealing-with-dbnull/2328/2
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | DataRow |
| Accepted Values | DataSet,DataTable,DataRow,PSObject,PSObjectArray,SingleValue |
Specifies a hashtable of parameters or output from New-DbaSqlParameter for parameterized SQL queries. http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
| Alias | SqlParameters |
| Required | False |
| Pipeline | false |
| Default Value |
Specifies the type of command represented by the query string. Valid options for this parameter are 'Text', 'TableDirect', and 'StoredProcedure'.
Default is 'Text'. Further information: https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtype
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | Text |
If this switch is enabled, the SQL Server instance will be appended to PSObject and DataRow output.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Use this switch to have on the output stream messages too (e.g. PRINT statements). Output will hold the resultset too.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
A collection of databases (such as returned by Get-DbaDatabase)
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
Execute the query with ReadOnly application intent.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Use this switch to prepend SET NOEXEC ON and append SET NOEXEC OFF to each statement, useful for checking query formal errors
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Appends to the current connection string. Note that you cannot pass authentication information using this method. Use -SqlInstance and optionally -SqlCredential to set authentication information.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |