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!
Course Of The Month
9 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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.
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.
[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
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
}
[string]$datasource = 'localhost'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = $new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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.sqlc
+ ~~~~~~~
Unexpected token '-object' in expression or statement.
At line:4 char:21
+ $conn = $new-object system.data.sqlclient.sqlc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'system.data.sqlclient.sql
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: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = $new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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.sqlc
+ ~~~~~~~
Unexpected token '-object' in expression or statement.
At line:4 char:21
+ $conn = $new-object system.data.sqlclient.sqlc
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token 'system.data.sqlclient.sql
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: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
Apologies -fat-fingered that line,
$conn = $new-object system.data.sqlclient.sqlc
should read:
$conn = new-object system.data.sqlclient.sqlc
$conn = $new-object system.data.sqlclient.sqlc
should read:
$conn = new-object system.data.sqlclient.sqlc
[string]$datasource = 'ew-s-ssis'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
That's just whatever you want to do if/when an exception is thrown.
Here's the full code, try this:
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
}
now i get this error:
At line:15 char:22
+ $sqlAdapter.Fill ($dt) | out-null
+ ~
Unexpected token '(' in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
[string]$datasource = 'localhost'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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.messag
}
At line:15 char:22
+ $sqlAdapter.Fill ($dt) | out-null
+ ~
Unexpected token '(' in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordE
+ FullyQualifiedErrorId : UnexpectedToken
[string]$datasource = 'localhost'
[string]$database = 'SSISDB'
[string]$connectionString = "Data Source=$dataSource;Integra
$conn = new-object system.data.sqlclient.sqlc
[system.data.sqlclient.sql
[system.data.sqlclient.sql
[system.data.datatable]$dt
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.messag
}
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
$sqlAdapter.Fill ($dt) | out-null
should be
$sqlAdapter.Fill($dt) | out-null
Featured Post
![[Webinar] How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/20/3315d9d8-8110-4d52-be94-a5aacdd83e4b.png)
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.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 5 hours left to enroll
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.