Powershell SQL SELECT output to variable

Question

I am calling a function to query an SQL table. I only need the results of one cell. I am unable to successfully retrieve the cell data into a variable from the function.

For example, If I had a table with the following:

    FeedID    Name    Address
    15        Bill    Jones

I would need to capture the FeedID value of '15' into a variable. My SQL statement is only capturing the FeedID but I don't know how to extract the value

Here is what I have so far:

    function Invoke-SQL {
    param(
    [string] $dataSource = "10.0.100.1",
    [string] $database = "Database123",
    [string] $sqlCommand = $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")
    )

    $connectionString = "Data Source=$dataSource; " + "Integrated Security=SSPI; " + "Initial Catalog=$database"
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    write-output $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables
    }

    $FeedID = Invoke-SQL
    $FeedID

What's is the content of $FeedID after the execution?

CB.
– CB.

2014-03-28 13:56:04 +00:00
Commented Mar 28, 2014 at 13:56 — CB.
– CB., Commented Mar 28, 2014 at 13:56
FeedID and the number 15

GreetRufus
– GreetRufus

2014-03-28 14:38:23 +00:00
Commented Mar 28, 2014 at 14:38 — GreetRufus
– GreetRufus, Commented Mar 28, 2014 at 14:38

Raf · Accepted Answer · 2021-01-05 16:33:39Z

22

Alternatively you could use the following code, if you are looking for simple return values rather than tables for processing later.

[string] $Server= "10.0.100.1",
[string] $Database = "Database123",
[string] $SQLQuery= $("SELECT [FeedID] FROM [dbo].[FeedList] WHERE [FeedFileName] = 'filename.txt'")

function GenericSqlQuery ($Server, $Database, $SQLQuery) {
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
         $Reader.GetValue(0)
    }
    $Connection.Close()
}

edited Jan 5, 2021 at 16:33

answered Mar 28, 2014 at 14:29

Raf

10.2k1 gold badge34 silver badges42 bronze badges

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

1 Comment

Hipponax43 Over a year ago

This only works because $1 is undefined and is taken as 0 when used in $Reader.GetValue($1)

Mike Shepard · Accepted Answer · 2014-03-28 14:06:20Z

13

It looks like $FeedID (the variable you put the SQL output in) should have a FeedID property (from the single row returned).

Try this:

$FeedID.FeedID

answered Mar 28, 2014 at 14:06

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

2 Comments

aitch-hat Over a year ago

This is so much simpler and more idiomatic than the accepted answer.

Alexander Swann Over a year ago

This answer is awesome because it actually answers the question. Less is more. Another upvote for you.

Collectives™ on Stack Overflow

Powershell SQL SELECT output to variable

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related