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

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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]

  • matt stockham

    SSCrazy Eights

    Points: 9892

    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: 57030

    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]

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

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