Jan 312008

I am late to the game on this one.

I am so glad it is going to be in Windows Server 2008 by default.

I wanted to do a simple task:
Export two columns in a table in a SQL Server 2005 database to a xml file named by the first column and fill the file with the second column that contains the XML.

Here is the code, notice the simplicity.

PS> is the Powershell command prompt aka each PS> is a line of text to cut and paste or type into Powershell.

You can copy to Notepad and make edits and then post into Powershell.

Remove the PS>, edit your SQL Server name, your user and password and the drive letter (L:\) you want it to save the files to and select all, copy and then past via the Powershell Upper Left Icon Menu to edit paste it all without having to type it line by line.

PS>$svr = “SQLServer”
PS>$cstr = “Data Source=$svr;Initial Catalog=OperationsManager;Persist Security Info=True;User ID=sa;Password=yourpassword”
PS> $dt = new-object “System.Data.DataTable”
PS> $cn = new-object System.Data.SqlClient.SqlConnection $cstr
PS> $cn.Open()
PS> $sql = $cn.CreateCommand()
PS> $sql.CommandText = “select MPFriendlyName,MPXML from dbo.ManagementPack”
PS> $rdr = $sql.ExecuteReader()
PS> $dt.Load($rdr)
PS> $cn.Close()
PS> foreach ($row in $dt) { $row.MPXML out-file -filepath(“l:\”+$row.MPFriendlyName+”.xml”) -encoding “utf8” }

Here are some links to review to get started.

Download Windows Powershell from here: https://www.microsoft.com/technet/scriptcenter/topics/msh/download.mspx
Visit the Windows Powershell at Technet : http://technet.microsoft.com/en-us/library/bb978526.aspx
Visit the Windows PowerShell Team blog at: http://blogs.msdn.com/PowerShell
Visit the Windows PowerShell ScriptCenter at: https://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

I will add more about how this works and add references to all the classes used and what this does for System Center Operations Manager 2007 MP Authors.