Windows Vista Forums
Vista Forums Home Join Vista Forums Windows 7 Forum Vista Tutorials Tags
Welcome to Windows Vista Forums. Our forum is dedicated to helping you find solutions with any problems, errors or issues you are experiencing with Windows Vista. The Vista forum also covers news and updates and has an extensive Windows Vista tutorial section that covers a wide range of tips and tricks.

Go Back   Vista Forums > Misc Newsgroups > PowerShell

Vista - Getting Powershell output into a sql table

Reply
 
Old 10-02-2008   #1 (permalink)
Blue Sky


 
 

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
Old 10-04-2008   #2 (permalink)
Shay Levy [MVP]


 
 

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
Reply

Thread Tools


Similar Threads
Thread Forum
table type formatted output in powershell PowerShell
PowerShell output 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


Vista Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows Vista", the Start Orb, and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46