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
34 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

756 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