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
37 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
  • 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
Ready to trade in that old firewall?

Whether you need to trade-up to a shiny new Firebox or just ready to upgrade from whatever appliance you're using now, WatchGuard has the right appliance for you! Find your perfect Firebox today with appliance sizing tool!

 

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 500 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 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