CODEWITHSUNDEEP
sqldatabasepowershell
JSkyS
JSkyS

Reputation: 443

Why is this powershell code only returning one result when I have multiple results?

I am running a query through powershell. I know I have multiple results from running the query through ssms. Why does the variable only have one result in powershell?

I have used many methods to do this query and I finally got it working but can't get all the results of the query.

[string] $Server= "mochqdb01";
[string] $Database = "MoCApp.Models.Item+ItemDBContext";
[string] $SQLQuery= $("Select smEmail from Items where DateRequested >= dateadd(day,datediff(day,1,GETDATE()),0)");

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()
Write-output "Database is " $dbname

Output: Database is [email protected]

Should have multiple results. Should I save into an array?

I actually want to save the results into this format.

Send-ToEmail -email "[email protected]","[email protected]";Is this possible?

Upvotes: 0

Views: 649

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

ExecuteScalar() returns the first column of the first row of the first result set. You need ExecuteReader(). EG

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLQuery
$SqlCmd.Connection = $SqlConnection
$rdr = $SqlCmd.ExecuteReader()
while ($rdr.Read())
{
   $smMail = $rdr[0]
   write-output "email is $smMail"
}
$rdr.Close()
$SqlConnection.Close()

Upvotes: 1

Related Questions