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
The code is: $var1 = 'string'; function Get-ODBC-Data{ param( [string]$query=$(' SELECT COUNT(*) FROM [master]. [sys]. [table_name] WHERE col2 = ''$var1'' ; '), [string]$username='db_user_name', [string]$password='db_password' ) $conn = New-Object System.
Description. The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. The commands supported are Transact-SQL statements and the subset of the XQuery syntax that is supported by the database engine.
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()
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With