I really have two questions but I'll ask the other (less important) one in
another thread.
I've been reading a lot about people struggling with the return dataset from
SQL in v1 and how powershell doesn't really return object sets but rather a
single rowset, even if there are several return rows -- I think I'm
describing that correctly, but sufficed to say I count myself among them.

Anyway, here is my problem/question:

Scenario: I need to run a simple query against a table that will return
multiple rows and column data; easy enough (see below).
Example:
$svr="HELPER"
$dbname="SAMPSON"
$query="select * from ServerConfig"

$Con = New-Object System.Data.SqlClient.SQLConnection
$Con.ConnectionString = "Server=$svr;Database=$dbname;Integrated
Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $Con
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$Con.Close()
$DataSet.Tables[0] | format-table -auto



This seems to be where a lot of code exampels and discussion I have looked
at stop. And I do see examples showing that you can return a specific
position of data (Row0 Col1, etc), but this is not helpful to me (yet?) since
I do not know how to get Col1, 2, 3 etc in to variables to act upon.
Hopefully this is just my lack of knowledge with PS scripting, as I am pretty
new.

Continuing the scenario:
I then need to process those rows of data in a foreach (or foreach-object?)
routine using the discrete column data as variables in the routine - this is
where I stumble because of how powershell treats the returned data.

ServerName Property ActiveFLAG
---------- ---------- ----------
HELPER Production Y
BROCK Production N
RUSTY Development Y

Taking then the ServerName in to $ThisServer, and $ThisProperty, $ThisFlag
and process each row with my conditions (If $ThisProperty -eq "Production" do
X else ...)

Am I barking up the wrong tree by asking PS to handle this? Like I said I'm
new to PowerShell and am hoping working with SQL datasets isn't prohibitively
complicated.

For all those who got to the end of my rambling, thanks!!! I really
appreciate any help you can give.
-JS

Ah, i think i figured it out!
Returned the table dataset to $ret and added as so:

$ret=$DataSet.Tables[0]
foreach ($set in $ret)
{
$thisserver= $set.servername
$thisprop = $set.property
write-Host "found $thisserver $thisprop"
}

Where in the foreach routine would be my scriptblock to apply my conditional
logic and actions wherei now have write-host.

I would still love to hear if anyone here has any tips or suggestions for
working with SQL output in powershell as I will be doing quite a bit of it.

"JS" wrote:

> I really have two questions but I'll ask the other (less important) one in
> another thread.
> I've been reading a lot about people struggling with the return dataset from
> SQL in v1 and how powershell doesn't really return object sets but rather a
> single rowset, even if there are several return rows -- I think I'm
> describing that correctly, but sufficed to say I count myself among them.
>
> Anyway, here is my problem/question:
>
> Scenario: I need to run a simple query against a table that will return
> multiple rows and column data; easy enough (see below).
> Example:
> $svr="HELPER"
> $dbname="SAMPSON"
> $query="select * from ServerConfig"
>
> $Con = New-Object System.Data.SqlClient.SQLConnection
> $Con.ConnectionString = "Server=$svr;Database=$dbname;Integrated
> Security=True"
> $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
> $SqlCmd.CommandText = $Query
> $SqlCmd.Connection = $Con
> $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
> $SqlAdapter.SelectCommand = $SqlCmd
> $DataSet = New-Object System.Data.DataSet
> $SqlAdapter.Fill($DataSet)
> $Con.Close()
> $DataSet.Tables[0] | format-table -auto
>
> This seems to be where a lot of code exampels and discussion I have looked
> at stop. And I do see examples showing that you can return a specific
> position of data (Row0 Col1, etc), but this is not helpful to me (yet?) since
> I do not know how to get Col1, 2, 3 etc in to variables to act upon.
> Hopefully this is just my lack of knowledge with PS scripting, as I am pretty
> new.
>
> Continuing the scenario:
> I then need to process those rows of data in a foreach (or foreach-object?)
> routine using the discrete column data as variables in the routine - this is
> where I stumble because of how powershell treats the returned data.
>
> ServerName Property ActiveFLAG
> ---------- ---------- ----------
> HELPER Production Y
> BROCK Production N
> RUSTY Development Y
>
> Taking then the ServerName in to $ThisServer, and $ThisProperty, $ThisFlag
> and process each row with my conditions (If $ThisProperty -eq "Production" do
> X else ...)
>
> Am I barking up the wrong tree by asking PS to handle this? Like I said I'm
> new to PowerShell and am hoping working with SQL datasets isn't prohibitively
> complicated.
>
> For all those who got to the end of my rambling, thanks!!! I really
> appreciate any help you can give.
> -JS

OK i still have a problem here. This is driving me crazy, someone please help
me keep my sanity. (!)

I'm able to get the SQL query back from a function you see below as CallSQL
This returns into $webs as System.Data.DataRow, as expected.
I'm also able to use foreach or foreach-object against the servername, shown
here to simply validate I am getting the proper values:
----start----

>> . .\get_libs.ps1
>> $webs=
>> CallSQL HELPER SAMPSON "set nocount on; select servername from viwserverroles where value='Production'"
>> foreach ($web in $webs)
>> {
>> $thisweb=$web.servername
>> $thisweb
>> }

RUSTY
HELPER
----end----

Perfect, i thought - I'm good to go! Not so fast....

I discovered whenever I tried to use the $thisweb variable in the foreach
(or foreach-object, i tried both) for any processing it was passing what
appeared to be a null (or something else, this is the problem) row on the
first pass, and would abort processing.
To help debug this I replaced the a write-host as shown with its output here:

----begin----

>> foreach ($web in $webs)
>> {
>> $thisweb=$web.servername
>> write-Host "found servername $thisweb"
>> }

found servername
found servername RUSTY
found servername HELPER
----end----
The first return of $thisweb is unexpected to me, i don't know where it's
coming from. There are no NULL returns in SQL against the same query, in fact
this column doesn't allow them. I can't figure out how to workaround this but
it's got me dead in the water.
I've tried putting in an IF statement in the foreach scriptblock to filter
out what might be getting passed in to the first return on $thisweb, like
"`0" and "" but obviously I'm not getting it.

Here's the return from $webs for reference:
----begin----

>> $webs

2
servername
----------
RUSTY
HELPER
----end----

Remaining calm and thankful for all assistance,
JS

The [Data.SqlClient.SqlDataAdapter]'s Fill() Method returns an [int] indicating the amount of rows added/refreshed to the DataSet, you can tell by looking at its signature:

$sqlAdapter = new-object data.sqlClient.sqlDataAdapter

# v1
[regex]::split(($sqlAdapter.fill).value, '(?<=\)), ')

# v2 CTP2
($sqlAdapter.fill).value -split '(?<=\)), '

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #

Functions in PowerShell return all output produced within it. Some methods produce unexpected output that must be redirected, captured or cast to [void] in order to suppress it.

# change this statement in yoiur CallSQL function...

$SqlAdapter.Fill($DataSet)

# ...to any of the following:

# _redirect_ output to $null
$SqlAdapter.Fill($DataSet) > $null

# _capture_ output to a variable or $null
$rows = $SqlAdapter.Fill($DataSet)
$null = $SqlAdapter.Fill($DataSet)

# _cast_ output as [void])
[void]$SqlAdapter.Fill($DataSet)

--
Kiron

Thank you very much for this information. I didn't realize you could simply
redirect or cast the fill to suppress this kind of returned data, I will need
to absorb this with some experimentation but it worked like a charm in this
example. Very, very helpful. I particularly appreciate the thoroughness of
your response, I think I actually learned something This will help me *a
lot* in dealing with other returned datasets, no doubt!

Huge gold star for Kiron!
-JS

"Kiron" wrote:

> The [Data.SqlClient.SqlDataAdapter]'s Fill() Method returns an [int]
> indicating the amount of rows added/refreshed to the DataSet, you can tell
> by looking at its signature:
>
> $sqlAdapter = new-object data.sqlClient.sqlDataAdapter
>
> # v1
> [regex]::split(($sqlAdapter.fill).value, '(?<=\)), ')
>
> # v2 CTP2
> ($sqlAdapter.fill).value -split '(?<=\)), '
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
>
> Functions in PowerShell return all output produced within it. Some methods
> produce unexpected output that must be redirected, captured or cast to
> [void] in order to suppress it.
>
> # change this statement in yoiur CallSQL function...
>
> $SqlAdapter.Fill($DataSet)
>
> # ...to any of the following:
>
> # _redirect_ output to $null
> $SqlAdapter.Fill($DataSet) > $null
>
> # _capture_ output to a variable or $null
> $rows = $SqlAdapter.Fill($DataSet)
> $null = $SqlAdapter.Fill($DataSet)
>
> # _cast_ output as [void])
> [void]$SqlAdapter.Fill($DataSet)
>
> --
> Kiron
>