PowerShell script calling a TSQL query result

Question

I am having trouble with a PowerShell script calling a TSQL query result. I want the result of my SQL query to be assigned to the $Output variable as a single value, not a dataset. From my PowerShell script below the $Output variable returns…

$Output

Column1
--------
100

When I need the result to return a single value like the following…

$Output
100

Below is the PowerShell script I am using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source= myserver;Initial 
Catalog=mydatabase;Integrated Security=SSPI;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select 100"
$SqlCmd.Connection = $SqlConnection

$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCmd
$Dataset = new-object System.Data.Dataset
write-output $DataAdapter.Fill($Dataset) | Out-Null
$Output = $DataSet.Tables
$Output

Any help would be greatly appreciated!

Emilio Lucas Ceroleni · Accepted Answer · 2017-12-06 18:51:18Z

3

Is this what you're looking for?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source= myserver;Initial 
Catalog=mydatabase;Integrated Security=SSPI;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select 100"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$Output = $SqlCmd.ExecuteScalar()
$Output

$SqlCmd.ExecuteScalar() returns a scalar value. Hope it helps.

answered Dec 6, 2017 at 18:51

Emilio Lucas Ceroleni

1,6282 gold badges12 silver badges13 bronze badges

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

4 Comments

Mike Shepard Over a year ago

Definitely the way to go with a single value coming back.

CarlD Over a year ago

This exactly what I was looking for. It works but for some reason, It returns two values instead of one. Do you have any ideas on why is that?

Emilio Lucas Ceroleni Over a year ago

Have you executed it as is? If not, please share your code.

Emilio Lucas Ceroleni Over a year ago

Great! Glad I could help.

Collectives™ on Stack Overflow

PowerShell script calling a TSQL query result

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related