Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do you pull a data set out of sql and assign variables to subsets of the data set in powershell.

Posted on 2016-08-05
11
1 solution
Medium Priority
?
38 Views
Last Modified: 2016-10-10
How do you pull a data set out of sql and assign variables to subsets of the data set.
So in powershell i would like to run a query to grab some data and then go row by row to assign data to varibles.
The idea is I want check if a version of something before i publish and then check it after.
Like
Name            |  VersionNumber |
blabla.flmx       346

and i want to assign:
TempVar1 = Blabla.flmx
TempVar2 = 346


so i would have to iterate through the record set. There are about 14 rows in all so not that big.
And then I would have to check it after the publish happens to the original.
0
Comment
Question by:josephh610
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 8
  • 3
11 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 41745455
Depending on a few potential adjustments for your environment setup, something like this should work...

[string]$datasource = 'sqlserver'
[string]$database = 'databasename'
[string]$connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database"
$conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)

[system.data.sqlclient.sqlcommand]$cmd = new-object system.data.sqlclient.sqlcommand
[system.data.sqlclient.sqldataAdapter]$sqlAdapter = new-object system.data.sqlclient.sqldataadapter
[system.data.datatable]$dt = new-object system.data.datatable
try {
  [void]$conn.open()
  $cmd.Connection = $conn
  $cmd.Commandtext = 'Select * From Table1'
  $sqlAdapter.SelectCommand = $cmd
  $sqlAdapter.Fill ($dt) | out-null

foreach ($record in $dt.rows) {
  $TempVar1 = $record.Name
  $TempVar2 = $record.VersionNumber
  $TempVar1 ; $TempVar2
}

Open in new window

0
 

Author Comment

by:josephh610
ID: 41747655
[string]$datasource = 'localhost'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database"
$conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)

[system.data.sqlclient.sqlcommand]$cmd = new-object system.data.sqlclient.sqlcommand
[system.data.sqlclient.sqldataAdapter]$sqlAdapter = new-object system.data.sqlclient.sqldataadapter
[system.data.datatable]$dt = new-object system.data.datatable
try {
  [void]$conn.open()
  $cmd.Connection = $conn
  $cmd.Commandtext = 'Select TOP 14 * from [internal].[packages]'
  $sqlAdapter.SelectCommand = $cmd
  $sqlAdapter.Fill ($dt) | out-null

foreach ($record in $dt.rows) {
  $TempVar1 = $record.Name
  $TempVar2 = $record.Version_build
  $TempVar1 ; $TempVar2
}

I got this error:
At line:4 char:13
+ $conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)
+             ~~~~~~~
Unexpected token '-object' in expression or statement.
At line:4 char:21
+ $conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'system.data.sqlclient.sqlconnection' in expression or statement.
At line:14 char:20
+   $sqlAdapter.Fill ($dt) | out-null
+                    ~
Unexpected token '(' in expression or statement.
At line:9 char:5
+ try {
+     ~
Missing closing '}' in statement block.
At line:20 char:2
+ }
+  ~
The Try statement is missing its Catch or Finally block.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41747674
Apologies -fat-fingered that line,
$conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)
should read:
$conn = new-object system.data.sqlclient.sqlconnection ($connectionString)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Download the report
 

Author Comment

by:josephh610
ID: 41747734
[string]$datasource = 'ew-s-ssis'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database"
$conn = new-object system.data.sqlclient.sqlconnection ($connectionString)

[system.data.sqlclient.sqlcommand]$cmd = new-object system.data.sqlclient.sqlcommand
[system.data.sqlclient.sqldataAdapter]$sqlAdapter = new-object system.data.sqlclient.sqldataadapter
[system.data.datatable]$dt = new-object system.data.datatable
try {
  [void]$conn.open()
  $cmd.Connection = $conn
  $cmd.Commandtext = 'Select TOP 14 * from [internal].[packages]'
  $sqlAdapter.SelectCommand = $cmd
  $sqlAdapter.Fill ($dt) | out-null
 
foreach ($record in $dt.rows) {
  $TempVar1 = $record.Name
  $TempVar2 = $record.Version_build
  $TempVar1 ; $TempVar2
}

Now i'm getting this:
At line:14 char:20
+   $sqlAdapter.Fill ($dt) | out-null
+                    ~
Unexpected token '(' in expression or statement.
At line:9 char:5
+ try {
+     ~
Missing closing '}' in statement block.
At line:20 char:2
+ }
+  ~
The Try statement is missing its Catch or Finally block.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 2000 total points (awarded by participants)
ID: 41747744
That's just whatever you want to do if/when an exception is thrown.
Here's the full code, try this:

[string]$datasource = 'sqlserver'
[string]$database = 'databasename'
[string]$connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database"
$conn = new-object system.data.sqlclient.sqlconnection ($connectionString)

[system.data.sqlclient.sqlcommand]$cmd = new-object system.data.sqlclient.sqlcommand
[system.data.sqlclient.sqldataAdapter]$sqlAdapter = new-object system.data.sqlclient.sqldataadapter
[system.data.datatable]$dt = new-object system.data.datatable
try {
    [void]$conn.open()
    $cmd.Connection = $conn
    $cmd.Commandtext = 'Select * From Table1'
    $sqlAdapter.SelectCommand = $cmd
    $sqlAdapter.Fill ($dt) | out-null

    foreach ($record in $dt.rows) {
        $TempVar1 = $record.Name
        $TempVar2 = $record.VersionNumber
        $TempVar1 ; $TempVar2
    }
} catch {
    $error[0].exception.message
}

Open in new window

0
 

Author Comment

by:josephh610
ID: 41747789
now i get this error:
At line:15 char:22
+     $sqlAdapter.Fill ($dt) | out-null
+                      ~
Unexpected token '(' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

[string]$datasource = 'localhost'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integrated Security=SSPI;Initial Catalog=$database"
$conn = new-object system.data.sqlclient.sqlconnection ($connectionString)

[system.data.sqlclient.sqlcommand]$cmd = new-object system.data.sqlclient.sqlcommand
[system.data.sqlclient.sqldataAdapter]$sqlAdapter = new-object system.data.sqlclient.sqldataadapter
[system.data.datatable]$dt = new-object system.data.datatable
try {
    [void]$conn.open()
    $cmd.Connection = $conn
    $cmd.Commandtext = 'Select TOP 14 * from [internal].[packages]'
    $sqlAdapter.SelectCommand = $cmd
    $sqlAdapter.Fill ($dt) | out-null

    foreach ($record in $dt.rows) {
        $TempVar1 = $record.Name
        $TempVar2 = $record.Version_BUILD
        $TempVar1 ; $TempVar2
    }
} catch {
    $error[0].exception.message
}
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41748176
Hmm - I retyped that from another script, instead of copy/paste.  But I believe there should be no space after .Fill and the open parenthesis:
 $sqlAdapter.Fill ($dt) | out-null
should be
 $sqlAdapter.Fill($dt) | out-null
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41766725
Did that sort it for you?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41785947
Need any further assistance?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41807050
Hello?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 41836495
I believe this sorts the question, but the OP is MIA. :^)
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Learn More
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PowerShell Script to check file version across the network
Article by: Frank
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Installing SQL Server vNext on Linux: Part 1 (Prerequisites)
Article by: Yashwant
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
SQL - Selecting Rows and Columns
Video by: Zia
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
SQL - Updating and Deleting Data
Video by: Zia
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Advertise Here

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here