Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 2, 2007

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11

By Muthusamy Anantha Kumar aka The MAK

Export output to XML

Part 1 and Part 2 of this series discussed Power Shell installation and simple SMO, WMI cmdlets. Part 3 covered how to script PowerShell and connect to SQL Server. Part 4 explained how to use a PowerShell script to loop through the content of a file and connect to different servers. Part 5 examined creating a SQL Server database using PowerShell and SMO. Part 6 talked about backing up a SQL Server database using PowerShell and SMO. Part 7 illustrated how to list all of the objects in a database and part 8 demonstrated how to list all of the properties of the objects in a database using PowerShell and SMO. Part 9 of this article series illustrated how to use PowerShell and SMO to generate a script for database and tables. Part 10 illustrated creating the PowerShell script to generate a script for the database and tables.

This installment of the series illustrates how to use PowerShell cmdlets in conjunction with the SQL Server client and output redirection to export to a text file or XML file.

Let us assume we want to query any SQL Server table using transact sql and store the output in text format or in XML format. Using PowerShell cmdlets, SQL Server client connection and output redirection, this can be achieved very easily.

Let us create c:\ps\output.ps1 as shown below. [Refer Fig 1.1]



param 
(
  [string] $SQLServer,
  [string] $Database,
  [string] $outputType,
  [string] $filename,
  [string] $Query
)

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString =  
"Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}


This script can be executed as shown below. [Refer Fig 1.2]

./output "HOME\SQLEXPRESS" "VixiaTrack" "TEXT" "c:\test.txt" "Select dbid,name from sys.sysdatabases"


Fig 1.2

Parameters explained:

  • output is actually the output.ps1 script in the folder c:\ps
  • HOME is the hostname
  • SQLEXPRESS is the sql server instance name on the host HOME
  • VixiaTrack is the database name that resides in the SQLEXPRESS instance
  • TEXT is the ouput format desired. It could be TEXT or XML

  • C:\test.txt is the filename and its location
  • Select dbid,name from sys.sysdatabases is the actual Transact SQL query executed against the database

When the PowerShell script is executed, it queries the database and saves the output to a text file that has been passed as parameter. [Refer Fig 1.3 and Fig 1.4]


Fig 1.3

Content of the test.txt file

dbid name          
---- ----          
   1 master        
   2 tempdb        
   3 model         
   4 msdb          
   5 test          
   6 VixiaTrack    
   7 XMLTest       
   8 admin         
   9 AdventureWorks


Fig 1.4

The same PowerShell script can be executed using XML as the parameter in order to generate the result in XML format.

This script can be executed as shown below. [Refer Fig 1.5]

./output "HOME\SQLEXPRESS" "VixiaTrack" "XML" "c:\test.xml" "Select dbid,name from sys.sysdatabases"


Fig 1.5

Parameters explained:

  • output is actually the output.ps1 script in the folder c:\ps
  • HOME is the hostname
  • SQLEXPRESS is the sql server instance name on the host HOME
  • VixiaTrack is the database name that resides in SQLEXPRESS instance
  • XML is the ouput format desired. It could be TEXT or XML.

  • C:\test.txt is the filename and its location.
  • Select dbid,name from sys.sysdatabases is the actual Transact SQL query executed against the database

When the PowerShell script is executed, it queries the database and saves the output to a XML file that has been passed as parameter. [Refer Fig 1.6 and Fig 1.7]


Fig 1.6

Content of the test.xml file

- <Objs Version="1.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
- <Obj RefId="RefId-0">
- <TN RefId="RefId-0">
  <T>System.Data.DataRow</T> 
  <T>System.Object</T> 
  </TN>
- <Props>
  <I16 N="dbid">1</I16> 
  <S N="name">master</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">2</I16> 
  <S N="name">tempdb</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">3</I16> 
  <S N="name">model</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">4</I16> 
  <S N="name">msdb</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">5</I16> 
  <S N="name">test</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">6</I16> 
  <S N="name">VixiaTrack</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">7</I16> 
  <S N="name">XMLTest</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">8</I16> 
  <S N="name">admin</S> 
  </Props>
  </Obj>
- <Obj RefId="RefId-0">
  <TNRef RefId="RefId-0" /> 
- <Props>
  <I16 N="dbid">9</I16> 
  <S N="name">AdventureWorks</S> 
  </Props>
  </Obj>
  </Objs>


Fig 1.7

Conclusion

Part 11 of this article series illustrated how to use PowerShell cmdlets in conjunction with the SQL Server client and output redirection to export to a text file or XML file.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date