powershell : output is only showing number of rows affected for a sql script

Question

I am running a very simple TSQL code in powershell. But the output only shows number of affected rows but not the actual results. Code is as follows:

function test([string] $Hostname )
{
    try
    {
    $con="server=$Hostname;database=master;Integrated security=sspi"
    $da=New-Object System.Data.SqlClient.SqlDataAdapter ("                      
                 select * from sysdatabases 
                ",$con)
    $dt=New-Object System.Data.DataTable
    $da.fill($dt)|Out-Null

    $da.fill($dt) | Format-table -AutoSize
    }
    catch [Exception]
    {
        ## Do something with the exception 
        write-host "Could not run test function for "+$Hostname
    }

}
foreach ($Hostname in Get-Content $ServerList)
{
    test($Hostname)
}

Any help please?

Thanks in advance

Personally, I'd write a simple .bat file that calls osql ;) But I don't see where you iterate through any of the resultset (either $da, or $dt). I suspect all that data is actually there: all you have to do is display it. — paulsm4
– paulsm4, Commented Jun 24, 2012 at 21:51
yes. i have written couple of scripts but never had this problem...please help — Manjot
– Manjot, Commented Jun 24, 2012 at 21:56

alroc · Accepted Answer · 2012-06-25 01:18:06Z

1

I would recommend installing the SQL Client tools and use SqlServerCmdletSnapin100 instead of having to create & manage everything via System.Data.SqlClient. Then your code will become:

function test([string] $Hostname )
{
    try {
        invoke-sqlcmd -server $hostname -database master -query "select * from sysdatabases" |ft -auto
    } catch [Exception] {
        ## Do something with the exception 
        write-host "Could not run test function for "+$Hostname
    }
}

SqlDAtaAdapter.Fill() returns the number of rows that were filled, which is why you get what you're getting. If you want to continue this way, you'll need to iterate through the DataTable that you've filled, as paulsm4 points out.

answered Jun 25, 2012 at 1:18

alroc

28.3k6 gold badges55 silver badges106 bronze badges

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

2 Comments

Manjot Over a year ago

Hi, when I use invoke-sqlcmd and try to format it as table then it says "The pipeline has been stopped." any idea?

alroc Over a year ago

Can't say I've ever seen that. Does it give any more detail? the exact exception may shed some light.

Collectives™ on Stack Overflow

powershell : output is only showing number of rows affected for a sql script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related