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

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.
josephh610Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
sirbountyConnect With a Mentor Commented:
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
 
sirbountyCommented:
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
 
josephh610Author Commented:
[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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Put the Hammer Down
 
sirbountyCommented:
Apologies -fat-fingered that line,
$conn = $new-object system.data.sqlclient.sqlconnection ($connectionString)
should read:
$conn = new-object system.data.sqlclient.sqlconnection ($connectionString)
0
 
josephh610Author Commented:
[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
 
josephh610Author Commented:
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
 
sirbountyCommented:
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
 
sirbountyCommented:
Did that sort it for you?
0
 
sirbountyCommented:
Need any further assistance?
0
 
sirbountyCommented:
Hello?
0
 
sirbountyCommented:
I believe this sorts the question, but the OP is MIA. :^)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.