Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4

July 11, 2007

Part I and Part II of this series discussed Power Shell installation and simple SMO, WMI cmdlets. Part III discussed how to script PowerShell and connect to SQL Server. This installment illustrates how to use PowerShell script to loop through the content of the file and connect to different servers.

Let us assume that we want to connect to different SQL Servers and collect certain information, such as the Server name, Version, and all database names.

Step 1

We need to create a PowerShell script that displays all of the above information for one server. Create connect2.ps1 as shown below. [Refer Fig 1.0]

param (
  [string] $SQLSERVER
)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 
 "Server=$SQLSERVER;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select 'Servername: 
 '+@@servername as Result union Select 'Version:'+  
@@version as Result union select 'Database:' +name from sysdatabases as Result
order by Result desc "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Click for larger image

Fig 1.0

Step 2

Execute the script shown below. [Refer 1.1]

./connect2 "HOME"

Note: HOME is the server name. Please change it to your server name.

Result

Result 
------
Version:Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) ...
Servername: HOME
Database: VixiaTrack
Database: vixiadata
Database: vixia
Database: TrackEquipment
Database: test
Database: tempdb
Database: Sales2
Database: Sales
Database: pubs
Database: Northwind
Database: msdb
Database: model
Database: master
Database: Legacy_Vixia
Database: abc3
Database: abc2
Database: abc


Fig 1.1

Step 3

Let us loop through a file and display it contents. Create the loop1.ps1 script shown below. [Refer Fig 1.2] Also, create the serverlist.txt file shown below. [Refer Fig 1.3]

param (
  [string] $filename 
)
$computers = get-content $filename
foreach ($computer in $computers) 
{
write-host $computer
}


Fig 1.3

Serverlist.txt

HOME
HOME\SQLEXPRESS


Fig 1.3

Let's execute the loop1.ps1 script shown below. [Refer Fig 1.4]

./loop1 serverlist.txt


Fig 1.4

Step 4

Now let's combine connect2.ps1 and loop1.ps1 to get the SQL Server name, SQL Server version and all the databases on the server names listed in serverlist.txt

Create connect3.ps1 as shown below. [Refer Fig 1.5]

param (
  [string] $filename 
)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content $filename
foreach ($computer in $computers) 
{
write-host "Details of the Server :" $computer
write-host "-----------------------------------"

$SqlConnection.ConnectionString = "Server=$computer;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select 'Servername: '+@@servername as Result union Select 'Version: '+@@version as Result union select 'Database:' 
+name from sysdatabases as Result
order by Result desc "
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
}


Fig 1.5

Step 5

Now, let's execute the script shown below. [Refer Fig 1.6]

./connect3 serverlist.txt


Fig 1.6

Result:

Details of the Server : HOME
-----------------------------------
19
Result
------
Version: Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) ...
Servername: HOME
Database: VixiaTrack
Database: vixiadata
Database: vixia
Database: TrackEquipment
Database: test
Database: tempdb
Database: Sales2
Database: Sales
Database: pubs
Database: Northwind
Database: msdb
Database: model
Database: master
Database: Legacy_Vixia
Database: abc3
Database: abc2
Database: abc
Details of the Server : HOME\SQLEXPRESS
-----------------------------------
11
Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) ...
Servername: HOME\SQLEXPRESS
Database: XMLTest
Database: VixiaTrack
Database: test
Database: tempdb
Database: msdb
Database: model
Database: master
Database: AdventureWorks
Database: admin

Now let's create a PowerShell script that does the same thing, using SQL Server SMO.

Step 1

Create the connect4.ps1 PowerShell script shown below [Refer Fig 1.7]

param 
(
  [string] $filename 
)
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$computers = get-content $filename
foreach ($computer in $computers) 
{
write-host "Details of the Server :" $computer
write-host "-----------------------------------"
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$computer"
write-host "Server Version: " $Server.Serverversion
write-host "Server Name: " $Server.Information.VersionString
foreach($database in $Server.databases) 
{
write-host "Database:" $database.name
}
}


Fig 1.7

Step2

Execute the above created connect4.ps1 script, as shown below. [Refer Fig 1.8]

./connect4 serverlist.txt


Fig 1.8

Result

PS C:\ps> ./connect4 serverlist.txt
GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\9...
Details of the Server : HOME
-----------------------------------
Server Version:
Server Name:  8.00.2039
Database: abc
Database: abc2
Database: abc3
Database: Legacy_Vixia
Database: master
Database: model
Database: msdb
Database: Northwind
Database: pubs
Database: Sales
Database: Sales2
Database: tempdb
Database: test
Database: TrackEquipment
Database: vixia
Database: vixiadata
Database: VixiaTrack
Details of the Server : HOME\SQLEXPRESS
-----------------------------------
Server Version:
Server Name:  9.00.2047.00
Database: admin
Database: AdventureWorks
Database: master
Database: model
Database: msdb
Database: tempdb
Database: test
Database: VixiaTrack
Database: XMLTest

Conclusion

Part IV of this article illustrated how to use a PowerShell script to loop through the content of a file and connect to different servers. It also illustrated how to do the same using SQL Server SMO.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers