Invoke‑SqlDscQuery

SYNOPSIS

Executes a query on the specified database.

SYNTAX

ByServerName (Default)

Invoke-SqlDscQuery [-ServerName <String>] [-InstanceName <String>] [-Credential <PSCredential>]
 [-LoginType <String>] [-Encrypt] -DatabaseName <String> -Query <String> [-PassThru]
 [-StatementTimeout <Int32>] [-RedactText <String[]>] [-Force] [-WhatIf]
 [-Confirm] [<CommonParameters>]

ByServerObject

Invoke-SqlDscQuery -ServerObject <Server> -DatabaseName <String> -Query <String> [-PassThru]
 [-StatementTimeout <Int32>] [-RedactText <String[]>] [-Force] [-WhatIf]
 [-Confirm] [<CommonParameters>]

DESCRIPTION

Executes a query on the specified database.

EXAMPLES

EXAMPLE 1

$serverObject = Connect-SqlDscDatabaseEngine
Invoke-SqlDscQuery -ServerObject $serverObject -DatabaseName 'master' `
    -Query 'SELECT name FROM sys.databases' -PassThru

Connects to the default instance and then runs a query to return all the database names in the instance.

EXAMPLE 2

$serverObject = Connect-SqlDscDatabaseEngine
$serverObject | Invoke-SqlDscQuery -DatabaseName 'master' `
    -Query 'RESTORE DATABASE [NorthWinds] WITH RECOVERY'

Connects to the default instance and then runs the query to restore the database NorthWinds.

EXAMPLE 3

$serverObject = Connect-SqlDscDatabaseEngine
Invoke-SqlDscQuery -ServerObject $serverObject -DatabaseName 'master' `
    -Query "select * from MyTable where password = 'PlaceholderPa\ssw0rd1' and password = 'placeholder secret passphrase'" `
    -RedactText @('PlaceholderPa\sSw0rd1','Placeholder Secret PassPhrase') `
    -PassThru -Verbose

Shows how to redact sensitive information in the query when the query string is output as verbose information when the parameter Verbose is used. For it to work the sensitiv information must be known and passed into the parameter RedactText. If any single character is wrong the sensitiv information will not be redacted. The redaction is case-insensitive.

EXAMPLE 4

Invoke-SqlDscQuery -ServerName Server1 -InstanceName MSSQLSERVER -DatabaseName 'master' `
    -Query 'SELECT name FROM sys.databases' -PassThru

Connects to the default instance and then runs a query to return all the database names in the instance.

PARAMETERS

-Credential

Specifies the credentials to use to impersonate a user when connecting. If this is not provided then the current user will be used to connect to the SQL Server Database Engine instance.

Type: PSCredential
Parameter Sets: ByServerName
Aliases: DatabaseCredential, SetupCredential

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-DatabaseName

Specifies the name of the database to execute the T-SQL query in.

Type: String
Parameter Sets: (All)
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-Encrypt

Specifies if encryption should be used.

Type: SwitchParameter
Parameter Sets: ByServerName
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Force

Specifies that the query should be executed without any confirmation.

Type: SwitchParameter
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-InstanceName

Specifies the instance name on which to execute the T-SQL query.

Type: String
Parameter Sets: ByServerName
Aliases:

Required: False
Position: Named
Default value: MSSQLSERVER
Accept pipeline input: False
Accept wildcard characters: False

-LoginType

Specifies which type of credentials are specified. The valid types are Integrated, WindowsUser, and SqlLogin. If WindowsUser or SqlLogin are specified then the Credential needs to be specified as well. Defaults to Integrated.

Type: String
Parameter Sets: ByServerName
Aliases:

Required: False
Position: Named
Default value: Integrated
Accept pipeline input: False
Accept wildcard characters: False

-PassThru

Specifies if the command should return any result the query might return.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: WithResults

Required: False
Position: Named
Default value: False
Accept pipeline input: False
Accept wildcard characters: False

-Query

The query string to execute.

Type: String
Parameter Sets: (All)
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-RedactText

One or more text strings to redact from the query when verbose messages are written to the console. Strings will be escaped so they will not be interpreted as regular expressions (RegEx).

Type: String[]
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-ServerName

Specifies the server name where the instance exist.

Type: String
Parameter Sets: ByServerName
Aliases:

Required: False
Position: Named
Default value: (Get-ComputerName)
Accept pipeline input: False
Accept wildcard characters: False

-ServerObject

Specifies current server connection object.

Type: Server
Parameter Sets: ByServerObject
Aliases:

Required: True
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: False

-StatementTimeout

Set the query StatementTimeout in seconds. Default 600 seconds (10 minutes).

Type: Int32
Parameter Sets: (All)
Aliases:

Required: False
Position: Named
Default value: 600
Accept pipeline input: False
Accept wildcard characters: False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi

Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

CommonParameters

This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.

INPUTS

OUTPUTS

[System.Data.DataSet] when passing parameter PassThru, otherwise

outputs none.

NOTES

RELATED LINKS