1.    02 Oct 2008 #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.    04 Oct 2008 #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


Similar Threads
Thread Forum
Format-Table output ?
Hello, In the script below how do I output the $objItem.Name and $objItem.DriverName using Format-Table instead of write-host or do I need to...
PowerShell
table type formatted output in powershell
Hi I see output of many commands coming in table format, and they look nice. is there any cmdlet that I can use to format my script's output...
PowerShell
PowerShell output
Hello, I've attempted to modify the script located at: http://www.microsoft.com/technet/scriptcenter/scripts/sus/default.mspx?mfr=true It lists...
PowerShell
How to output a powershell into a sql server 2008 table
Hy all, my english is veru bad so i try to explain I get this script in the net and works percetly ...
PowerShell
Cut the Output in Windows PowerShell
Hey Guys, I am System Admin and i know Batch Scripting and i use it for automating tasks. I want to know the Syntax of how can i i cut the output...
PowerShell
[demo] Format-Chart - Formats output as a table with a chart colum
The script formats output as a table with a pseudo-graphical chart column calculated for the last specified numeric property. It is here:...
PowerShell
Trying to use the output as a table
I am not having alot of luck creating a table type object from the output of a file. I can force it into a table and export it to a file as csv...
PowerShell

Our Sites
  • Ten Forums
  • Eight Forums
  • Seven Forums
  • PC Help Forum
  • Help Me Bake
  • Site Links
  • Contact Us
  • Privacy and Cookies
  • About Us
    Windows Vista Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

    Designer Media Ltd
    All times are GMT -5. The time now is 05:44.
    .