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
35 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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 ?
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 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