Powershell SQL Query to variables

Question

Trying to use SQL results as variables. This will return the results to the host, but I would like to use each value as a variable.

$job = "22940"
$SQLServer = "my-sql" #use Server\Instance for named SQL instances! 
$SQLDBName = "MyDatabase"
$SqlQuery = "DECLARE @Job VARCHAR(5);
SET @Job = '$job'
SELECT        dbo.Job.Job, dbo.Job.Customer, 
              dbo.Job.Note_Text, 
              dbo.User_Values.Date1 AS 'Setup Date', 
              dbo.User_Values.Date2 AS 'Due Date'
FROM            dbo.Job INNER JOIN
                         dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
WHERE        (dbo.Job.Job = @Job) AND (dbo.Job.User_Values = dbo.User_Values.User_Values)"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

clear

$DataSet.Tables[0]

How can I put each column into a variable that I can use for something else?

You already have it all in an array? I'm not quite sure I understand what you are trying to do here... — notjustme
– notjustme, Commented Dec 10, 2018 at 16:40
I need column to be its own variable. Example I need dbo.Job.Job to equal $MyJobNumber How would I go about sorting this? — Eric
– Eric, Commented Dec 10, 2018 at 16:44
You already have variables.... though not in an array, as indicated in another comment. A datable is something different, even if it can be used in some of the same ways. You just need to know how to identify the variables you already have in the code. This will be easier for use to show if you if you can tell us how you want to use these variables. Show us the next section of code. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 10, 2018 at 16:46
Not sure how to code it, but I am looking to end up with 5 (one for each column) variables that I can output or use for something else. $MyJobNumber $MyCustomer $MyNotes $MySetupDate $MyDueDate — Eric
– Eric, Commented Dec 10, 2018 at 16:56
In your SQL statement, you don't need the second condition. The one that is already in your ON statement. It's redundant. Probably doesn't affect performance, but makes the SQL overly complicated. — Ed Callahan
– Ed Callahan, Commented Dec 10, 2018 at 20:18

Ed Callahan · Accepted Answer · 2018-12-10 18:15:16Z

I work with data tables directly. You should also look into handling parameters correctly. It helps with handling special characters in the variable value. And, it protects from SQL Injection Attacks:

$Sql = 'select top 100 first_name, Last_name from person where last_name=@lastname'
$Database = 'XXX'
$Server = 'XXX'

$LastName = 'Jones'

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$Connection.Open()

$cmd = new-object system.Data.SqlClient.SqlCommand($Sql, $Connection)

$null = $cmd.Parameters.AddWithValue('@lastname', $LastName)

$DataTable = New-Object System.Data.DataTable
$SqlDataReader = $cmd.ExecuteReader()
$DataTable.Load($SqlDataReader)

$Connection.Close()
$Connection = $null

$DataTable.Rows.Count

foreach ($r in $DataTable.Rows)
{
    $fn = $r.first_name
    $ln = $r.last_name    

    "$fn $ln"
}

Eric · Accepted Answer · 2018-12-10 17:38:41Z

Not sure if this is really the correct way but it does give me the results I am looking for. Looked through some of my notes on working with CSV files and I added the foreach at the end. The above comments from @notjustme and @jcoehoorn got me looking in the right direction. Thanks

$job = "22940"
$SQLServer = "my-sql" #use Server\Instance for named SQL instances! 
$SQLDBName = "MyDatabase"
$SqlQuery = "DECLARE @Job VARCHAR(5);
SET @Job = '$job'
SELECT        dbo.Job.Job, dbo.Job.Customer, 
              dbo.Job.Note_Text, 
              dbo.User_Values.Date1 AS 'Setup Date', 
              dbo.User_Values.Date2 AS 'Due Date'
FROM            dbo.Job INNER JOIN
                         dbo.User_Values ON dbo.Job.User_Values = dbo.User_Values.User_Values
WHERE        (dbo.Job.Job = @Job) AND (dbo.Job.User_Values = dbo.User_Values.User_Values)"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

clear

foreach($Job in $DataSet.Tables[0])
     {
     $MyJobNumber = $Job.Job
     $MyCustomer = $Job.Customer
     $MyNotes = $Job.Note_Text
     $MySetupDate = $Job.Setup_Date
     $MyDueDate = $Job.Due_Date
     }

Collectives™ on Stack Overflow

Powershell SQL Query to variables

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related