Function that returns rowset, to feed a loop cycling through rows

Question

Function that returns rowset, to feed a loop cycling through rows

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
January 28, 2009 at 1:07 pm

#204730

Hi,
I want to write a function that returns a rowset and in turn feeds that to a loop.
Here is the pseudocode:
function getInstances()
{
$instance1 = "srvr\inst"
sqlcmd `-S $instance1 `-i getInstances.sql
}
$rowSet = getInstances
# $rowSet contains list of SQL instances
$i=0
while ($i < length of $rowSet)
{
sqlcmd `-S $rowSet[$i] `-i getInstanceInfo.sql
$i++
}
In short, the script gets the list of SQL instances into an array structure and cycles through that array in a loop executing a sqlcmd command in each iteration.
The problem I'm having is, I don't know how to store the output of the function to a rowset/array and feed that to a loop.
Anyone have any sample code like this to share?
Much appreciated.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 1

I added the -h parameter to sqlcmd to drop headers. You also need to set nocount or parse out the returned values to only return valid instances.

function getInstances()

{

$instance1 = "srvr\inst"

sqlcmd -S $instance1 -i getInstances.sql -h -1

}

foreach ($inst in getInstances) {

sqlcmd -S $inst -i getInstanceInfo.sql

}

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

matt stockham (1/28/2009)
I added the -h parameter to sqlcmd to drop headers. You also need to set nocount or parse out the returned values to only return valid instances.
function getInstances()
{
$instance1 = "srvr\inst"
sqlcmd -S $instance1 -i getInstances.sql -h -1
}
foreach ($inst in getInstances) {
sqlcmd -S $inst -i getInstanceInfo.sql
}

Thank you! I will give that a try.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]