CODEWITHSUNDEEP
powershell
MISNole
MISNole

Reputation: 1062

Passing a single value from a SQL query to a PowerShell variable

I am trying to a single value from a SQL query to a PowerShell variable. I know the connection and query are running successfully because I am getting the output I expected when using Invoke-DbaQuery or Invoke-Sqlcmd. But when I try to assign the query results to a variable, all I see is System.Data.DataRow

Any suggestions on how to get this query's results assigned to a variable?

$SQLInstance = "SQLInstance"
$Database = "DatabaseName"
$Query = "SELECT TOP (1) Password FROM dbo.Connections WHERE VendorName = 'YYY' ORDER BY VendorName;"

#Invoke-DbaQuery -SqlInstance $SQLInstance -Database $Database -Query $Query

#$password = (Invoke-DbaQuery -SqlInstance $SQLInstance -Database $Database -Query $Query)
$password = Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $Database -Query $Query

Write-Host $password

Upvotes: 1

Views: 3178

Answers (1)

Cpt.Whale
Cpt.Whale

Reputation: 5321

You've seen that $password is a [DataRow], and Write-Host can only use [string]. Try one of these two:

  • Output the result by itself (no write-host). Powershell will format all of the fields and rows for you:
$password

# outputs:

password
--------
hunter2
  • Output just the string from your query result instead of the table format above:
$password.Password

# Outputs:

hunter2

DataRow objects are made to contain multiple rows and multiple fields, so it can get confusing when you're expecting just a single value as a result.

Upvotes: 5

Related Questions