Windows Vista Forums

Getting Powershell output into a sql table

  1. #1


    Blue Sky Guest

    Getting Powershell output into a sql table

    As a Powershell newbie, i wanted to know if i can create a powershell app
    that will search my list of SQL Server Instances (from a sql table) query
    each one for the list of administrators of that server and then put that data
    into a SQL table.

    I finally found this function from Ron Dameron
    (http://www.simple-talk.com/content/a...icle=576#forum) which
    lists administrators of a server.

    Then, i found RIchard Siddaway's example of how to use powershell to push
    into a table.
    http://richardsiddaway.spaces.live.c...entry?0aaed4f8 Now, it seems I'm not too far from solution!

    Can anyone put this together for me to put the output of the function run
    against a list of servers into another SQL Table?

    If I had a solution to this, it will be my template for gathering all kinds
    of useful info!
    Thanks in advance!
    --
    The Spirit gives life; the flesh counts for nothing! (Jn 6:63)

      My System SpecsSystem Spec

  2. #2


    Shay Levy [MVP] Guest

    Re: Getting Powershell output into a sql table



    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

    $sqlServer = ".\SQLEXPRESS"
    $dbName = "TestDB"
    $tblAdmins = "AdminRoleMembers"


    # table to collect the information
    # create a table named $tblAdmins on $dbName (if the table doesn't exist)
    # with two columns, Server and Member

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlServer
    $db = $srv.databases[$dbName]

    if(!$db.tables[$tblAdmins]){

    $tbl = new-object Microsoft.SqlServer.Management.Smo.Table ($db,$tblAdmins)
    $col1=new-object Microsoft.SqlServer.Management.Smo.Column ($tbl,"Server",[Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50))
    $col2=new-object Microsoft.SqlServer.Management.Smo.Column ($tbl,"Member",[Microsoft.SqlServer.Management.Smo.DataType]::NVarChar(50))

    $col1.nullable = $false
    $col2.nullable = $false

    $tbl.columns.add($col1)
    $tbl.columns.add($col2)
    $tbl.create()

    } else {
    write-warning "skipping...table already exists"
    }



    ## open database connection
    $conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;
    Initial Catalog=$dbName; Integrated Security=SSPI")
    $conn.Open()
    $cmd = $conn.CreateCommand()


    # list the sysadmin role members foreach server name in servers.txt
    # and write the members to the $tblAdmins table


    get-content servers.txt | foreach {
    $sqlName = $_
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlName

    $srv.roles["sysadmin"].enumServerRoleMembers() | foreach {
    $cmd.CommandText = "INSERT INTO $tblAdmins VALUES ('$sqlName','$_')"
    $null = $cmd.ExecuteNonQuery()
    }
    }


    $conn.Close()





    ---
    Shay Levy
    Windows PowerShell MVP
    http://blogs.microsoft.co.il/blogs/ScriptFanatic
    PowerShell Toolbar: http://tinyurl.com/PSToolbar



    BS> As a Powershell newbie, i wanted to know if i can create a
    BS> powershell app that will search my list of SQL Server Instances
    BS> (from a sql table) query each one for the list of administrators of
    BS> that server and then put that data into a SQL table.
    BS>
    BS> I finally found this function from Ron Dameron
    BS> (http://www.simple-talk.com/content/a...icle=576#forum)
    BS> which lists administrators of a server.
    BS>
    BS> Then, i found RIchard Siddaway's example of how to use powershell to
    BS> push into a table.
    BS> http://richardsiddaway.spaces.live.c...6A74CF3E96!382
    BS> .entry?0aaed4f8 Now, it seems I'm not too far from solution!
    BS>
    BS> Can anyone put this together for me to put the output of the
    BS> function run against a list of servers into another SQL Table?
    BS>
    BS> If I had a solution to this, it will be my template for gathering
    BS> all kinds
    BS> of useful info!
    BS> Thanks in advance!



      My System SpecsSystem Spec


Getting Powershell output into a sql table
Similar Threads
Thread Forum
Format-Table output ? PowerShell
table type formatted output in powershell PowerShell
How to output a powershell into a sql server 2008 table PowerShell
[demo] Format-Chart - Formats output as a table with a chart colum PowerShell
Trying to use the output as a table PowerShell