Invoke‑SqlDscScalarQuery - dsccommunity/SqlServerDsc GitHub Wiki

Executes a scalar query on the specified server.

Invoke-SqlDscScalarQuery [-ServerObject] <Server> [-Query] <String> [[-StatementTimeout] <Int32>]
 [[-RedactText] <String[]>] [<CommonParameters>]

Executes a scalar query on the specified server using the server connection context. This command is designed for queries that return a single value, such as SELECT @@VERSION or SELECT SYSDATETIME().

The command uses Server.ConnectionContext.ExecuteScalar() which is server-level and does not require any database to be online.

$serverObject = Connect-SqlDscDatabaseEngine
Invoke-SqlDscScalarQuery -ServerObject $serverObject -Query 'SELECT @@VERSION'

Connects to the default instance and then runs a query to return the SQL Server version.

$serverObject = Connect-SqlDscDatabaseEngine
$serverObject | Invoke-SqlDscScalarQuery -Query 'SELECT SYSDATETIME()'

Connects to the default instance and then runs the query to return the current date and time from the SQL Server instance.

$serverObject = Connect-SqlDscDatabaseEngine
Invoke-SqlDscScalarQuery -ServerObject $serverObject -Query "SELECT name FROM sys.databases WHERE name = 'MyPassword123'" -RedactText @('MyPassword123') -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.

Specifies the scalar query string to execute.

Type: String
Parameter Sets: (All)
Aliases:

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

Specifies 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: 4
Default value: None
Accept pipeline input: False
Accept wildcard characters: False

Specifies current server connection object.

Type: Server
Parameter Sets: (All)
Aliases:

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

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

Type: Int32
Parameter Sets: (All)
Aliases:

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

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