Piping from Get-DbaRegServer to Invoke-DbaQuery while specifying -Database causes master to be queried, even when another database is specified

[ReeceGoding]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

You're most likely to get "Invalid object name 'YourSchema.YourTable'". This happens because piping from Get-DbaRegServer to Invoke-DbaQuery while specifying -Database causes master to be queried, even when another database is specified.

Steps to Reproduce

Get-DbaRegServer | Invoke-DbaQuery -Database MyDb -Query "SELECT * FROM dbo.TableOnlyInMyDb"`

This issue happens even if you filter Get-DbaRegServer down to only return one server.

Please confirm that you are running the most recent version of dbatools

Tested on 2.1.23

Other details or mentions

Piping in the server names as strings does not cause this issue. You have to pipe from Get-DbaRegServer.

-Verbose adds no more useful information.

Make sure that you don't hit this issue, which I'm pretty sure my PR didn't fix.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

5.1.19041.5007

SQL Server Edition and Build number

Doesn't seem to matter.

.NET Framework Version

Very recent.

[mattcargile]

So I dug into it more. I think it is related to Connect-DbaInstance -Database db usage. Take the below example. It returns master instead of msdb.

Get-DbaRegServer sqlmgmt | select -first 1 | connect-dbainstance -Database msdb | invoke-dbaquery -Query 'select db_name() d;'

So it appears this code path needs to add $Database handling like the above Server object is handled.

dbatools/public/Connect-DbaInstance.ps1

Lines 654 to 687 in 55f5166

	} elseif ($inputObjectType -in 'RegisteredServer', 'ConnectionString') {
	# Create the server SMO in the same way as when passing a string (see #8962 for details).
	# Best way to get connection pooling to work is to use SqlConnectionInfo -> ServerConnection -> Server
	$sqlConnectionInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo
	# Set properties of SqlConnectionInfo based on the used properties of the connection string.
	$csb = New-Object -TypeName Microsoft.Data.SqlClient.SqlConnectionStringBuilder -ArgumentList $connectionString
	if ($csb.ShouldSerialize('Data Source')) {
	Write-Message -Level Debug -Message "ServerName will be set to '$($csb.DataSource)'"
	$sqlConnectionInfo.ServerName = $csb.DataSource
	$null = $csb.Remove('Data Source')
	}
	if ($csb.ShouldSerialize('User ID')) {
	Write-Message -Level Debug -Message "UserName will be set to '$($csb.UserID)'"
	$sqlConnectionInfo.UserName = $csb.UserID
	$null = $csb.Remove('User ID')
	}
	if ($csb.ShouldSerialize('Password')) {
	Write-Message -Level Debug -Message "Password will be set"
	$sqlConnectionInfo.Password = $csb.Password
	$null = $csb.Remove('Password')
	}
	# Add all remaining parts of the connection string as additional parameters.
	$sqlConnectionInfo.AdditionalParameters = $csb.ConnectionString
	# Set properties based on used parameters.
	if ($TrustServerCertificate) {
	Write-Message -Level Debug -Message "TrustServerCertificate will be set to '$TrustServerCertificate'"
	$sqlConnectionInfo.TrustServerCertificate = $TrustServerCertificate
	}
	$serverConnection = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlConnectionInfo
	$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $serverConnection

There is $Database handling for $inputObjectType -eq 'Server'.

dbatools/public/Connect-DbaInstance.ps1

Lines 595 to 606 in 55f5166

	if ($Database) {
	Write-Message -Level Debug -Message "Database [$Database] provided."
	if (-not $inputObject.ConnectionContext.CurrentDatabase) {
	Write-Message -Level Debug -Message "ConnectionContext.CurrentDatabase is empty, so connection will be opened to get the value"
	$inputObject.ConnectionContext.Connect()
	Write-Message -Level Debug -Message "ConnectionContext.CurrentDatabase is now [$($inputObject.ConnectionContext.CurrentDatabase)]"
	}
	if ($inputObject.ConnectionContext.CurrentDatabase -ne $Database) {
	Write-Message -Level Verbose -Message "Database [$Database] provided. Does not match ConnectionContext.CurrentDatabase [$($inputObject.ConnectionContext.CurrentDatabase)], copying ConnectionContext and setting the CurrentDatabase"
	$copyContext = $true
	}
	}