Powershell working with SQL Server datasets

Question

I was trying to filter the dataset that i got from SQL server database. Here is the scenario...

I'm getting servername,dbname columns from one of the database servers and returning the result set as return ,$dataSet.Tables[0]. I'm getting the results correctly in a table format.

But now i got all the server names to a variable from this dataset as below,

$servers=$dataSet_1.servername | select  -unique

Now, i'm trying to loop through each server and get the database list associated to each server as follows, but looks like this is not a right approach as it is getting me all the severs and their database names is every iteration

foreach($server IN $servers)
{
     write-host $server
     $dataSet_1 | Where-Object {$dataSet_1.servername -eq $server} | select $_.dbname
}

Could someone help me the right approach are a way to do this.

Sample output: (Basically it should iterate each server and display its databasename)

ServerA

dbname

database1 database2 database3 ....

ServerB

dbname

database1 database8 database10 ....

ServerC ...

Thanks,

Please include sample data for input, expected output and actual output. — vonPryz
– vonPryz, Commented Oct 30, 2014 at 21:17
here is the output should look like, ServerA dbname ------ database1 database2 database3 ServerB dbname ------ database1 database5 database10 ServerC .... — S.D.
– S.D., Commented Oct 30, 2014 at 21:22
Please don't add data as comment. It is hard to read. Edit your question and add all the asked data. — vonPryz
– vonPryz, Commented Oct 30, 2014 at 21:26

hysh_00 · Accepted Answer · 2014-10-31 01:31:20Z

0

"$dataSet_1 | where {$_.servername -eq $server} " is still returning the System.Data.DataTable type.

Does this return the desired output?

$dataSet_1 | where {$_.servername -eq $server}  | %{$_.dbname}

answered Oct 31, 2014 at 1:31

hysh_00

8396 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

briantist · Accepted Answer · 2014-10-30 22:25:52Z

0

Maybe this:

$servers=$dataSet_1.servername | Select-Object -Unique
$filteredSet = $dataSet_1 | Where-Object { $servers -contains $_.servername }

$filteredSet

edited Oct 30, 2014 at 22:25

answered Oct 30, 2014 at 21:23

briantist

48.3k6 gold badges94 silver badges139 bronze badges

6 Comments

S.D. Over a year ago

when executing your code i got following exception.

Select-Object : Property "servername" cannot be found. At line:54 char:38 +     $servers=$dataSet_1.servername | Select-Object -Unique -ExpandProperty serve ... +                                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + CategoryInfo          : InvalidArgument: (SERVER-A:PSObject) [Select-Object], PSArgumentException     + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

S.D. Over a year ago

but the following code collects all servers from the dataset $servers=$dataSet_1.servername | select -unique From here how to recurse the servernames to get associated dbnames for each server in $servers.

briantist Over a year ago

I edited the code; does it work now? Please note it's difficult to tell what your $dataSet_1 object contains, so I'm going by context clues.

S.D. Over a year ago

I didn't get any exceptions now...but the code returns nothing. My dataset got two properties servername & dbname. I would like to recurse each server and collect database names associated to each server. something like .. ` foreach($server IN $servers) { $dataSet_1 | Where-Object { $_.servername -eq $server} | Select dbname }`

briantist Over a year ago

I have edited again. $filteredSet now contains the set. You should be able to just do $filteredSet to see the pairs of servernames and dbnames, or you can do $filteredSet.dbname to see just the dbnames.

|

Collectives™ on Stack Overflow

Powershell working with SQL Server datasets

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related