GreetRufus GreetRufus - 2 years ago 515
SQL Question

Powershell SQL SELECT output to variable

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 {
[string] $dataSource = "",
[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$connectionString)
$command = new-object$sqlCommand,$connection)

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


$FeedID = Invoke-SQL

Raf Raf
Answer Source

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

[string] $Server= "",
[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;"
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download