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

Question

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

Cpt.Whale · Accepted Answer · 2021-04-14 20:47:24Z

5

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.

answered Apr 14, 2021 at 20:47

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MISNole Over a year ago

Thank you for the explanation - that worked for me.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related