commands

^

Invoke-DbaQuery

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.

Synopsis

A command to run explicit T-SQL commands or files.

Description

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.

Syntax

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]
    [-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]
    [-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]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
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'

Example: 2
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]

Example: 3
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"

Example: 4
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"

Example: 5
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).

Example: 6
PS C:\> Invoke-DbaQuery -SqlInstance aglistener1 -ReadOnly -Query "select something from readonlydb.dbo.atable"

Executes a query with ReadOnly application intent on aglistener1.

Example: 7
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

Example: 8
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

Example: 9
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.

Example: 10
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.

Example: 11
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

Required Parameters

-Query

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
-File

Specifies the path to one or several files to be used as the query input.

Alias InputFile
Required True
Pipeline false
Default Value
-SqlObject

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

Optional Parameters

-SqlInstance

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
-SqlCredential

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
-Database

The database to select before running the query. This list is auto-populated from the server.

Alias
Required False
Pipeline false
Default Value
-QueryTimeout

Specifies the number of seconds before the queries time out.

Alias
Required False
Pipeline false
Default Value 0
-As

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
-SqlParameter

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
-CommandType

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
-AppendServerInstance

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
-MessagesToOutput

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
-InputObject

A collection of databases (such as returned by Get-DbaDatabase)

Alias
Required False
Pipeline true (ByValue)
Default Value
-ReadOnly

Execute the query with ReadOnly application intent.

Alias
Required False
Pipeline false
Default Value False
-NoExec

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
-EnableException

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