Month: February 2011

Yes!!  Yesterday, SAPIEN Technologies released their PrimalForms 2011 Beta program, and they are looking for testers:

http://blog.sapien.com/index.php/2011/02/15/primalforms-2011-beta-testers-needed/

Go on and participate!!  This is a very powerful tool.

Here’s some PowerShell code use to create a PS variable results from a T-SQL query using the SQL  Server 2008 (or higher) SQLPS command “Invoke-SQLCmd”:

Import-Module SQLPSV2 $SQL1 = @” Select * from SurfSpotTable “@ $SQL1Query = Invoke-Sqlcmd -Server ‘MAX-PCWIN1’ -Database ‘Developer’ $SQL1Query

As you can see, with a few lines of code we got back some table information:

$SQL1Query SpotID     SpotName            Location                      State ——       ——–                      ——–                            —–      1          Wildernes               Aguadilla                     PR      2         Aviones                   Carolina                       PR      3         Surfers Beach        Aguadilla                     PR      4         Jobos                        Isabela                         PR

There’s a couple of things to understand about this code:

1. You need to download either Chad’s Miller SQLPS module for SQL Server 2008 or my SQL Denali SQLPSv2 module version.

2. Need to use the Import-Module to load the SQL Server snapins.

3. Invoke-SQLCmd is using Windows Authentication to connect to the database.

Now, that we have our PS variable “$SQL1Query” then we use the Export-Clixml to export the information to a XML formatted file:

## Convert to XML $SQL1Query | Export-Clixml -Path createxml.xml -NoClobber -Encoding ASCII ii createxml.xml

Then, use an XML Editor such as SAPIEN Technologies PrimalXML 2009 to open the file and see the information.  The XML file will look like this:

<Objs Version=”1.1.0.1″ xmlns=”http://schemas.microsoft.com/powershell/2004/04″>   <Obj RefId=”0″>     <TN RefId=”0″>       <T>System.Data.DataRow</T>       <T>System.Object</T>     </TN>     <ToString>System.Data.DataRow</ToString>     <Props>       <I64 N=”SpotID”>1</I64>       <S N=”SpotName”>Wildernes</S>       <S N=”Location”>Aguadilla</S>       <S N=”State”>PR</S>     </Props>   </Obj>   <Obj RefId=”1″>     <TNRef RefId=”0″ />     <ToString>System.Data.DataRow</ToString>     <Props>       <I64 N=”SpotID”>2</I64>       <S N=”SpotName”>Aviones</S>       <S N=”Location”>Carolina</S>       <S N=”State”>PR</S>     </Props>   </Obj>   <Obj RefId=”2″>     <TNRef RefId=”0″ />     <ToString>System.Data.DataRow</ToString>     <Props>       <I64 N=”SpotID”>3</I64>       <S N=”SpotName”>Surfers Beach</S>       <S N=”Location”>Aguadilla</S>       <S N=”State”>PR</S>     </Props>   </Obj>   <Obj RefId=”3″>     <TNRef RefId=”0″ />     <ToString>System.Data.DataRow</ToString>     <Props>       <I64 N=”SpotID”>4</I64>       <S N=”SpotName”>Jobos</S>       <S N=”Location”>Isabela</S>       <S N=”State”>PR</S>     </Props>   </Obj> </Objs>

So, with a few lines of code we just produce an XML file from a SQL DataRow results.  You be the judge… but I think this is another example of PowerShell Awesomeness!!

.. I’m Just Saying!!

Stay tuned!! I will have more on PowerShell, SQL, and XML files soon.