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

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 3 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3

The MAK

Part I and Part II of this series showed simple Power Shell settings, SMO and WMI cmdlets. Part III instructs writing PowerShell and connecting to SQL Server. Part 4 will show you how to use PowerShell code to iterate file content and connect to other servers.

Imagine that we want to connect to different SQL Servers and collect the correct information like server name, version and all database names.

Step 1

We need to create a PowerShell script that displays all of the above information for a server. Create connect2.ps1 as shown below. [Figure 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]

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

Step 2

Execute the following code: [Figure 1.1]

./connect2 "HOME"

Note : HOME is the server name. Please replace 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

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 5
Figure 1.1

Step 3

Repeat through a file and display the contents of the file. Create the following loop1.ps1 code: [Figure 1.2] Also create the serverlist.txt file. [Figure 1.3]

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

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 6
Figure 1.2

Serverlist.txt

HOME
HOMESQLEXPRESS

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 7
Figure 1.3

Please execute the loop1.ps1 code. [Figure 1.4]

./loop1 serverlist.txt

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 8
Figure 1.4

Step 4

Now combine connect2.ps1 and loop1.ps1 to obtain the names of SQL Server, SQL Server version and all database names listed on serverlist.txt.

Create connect3.ps1 as follows: [Figure 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: ' $ 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] } + @@ 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 = $ SqlCmax.SelectCommand = $ SqlCmd $ DataSet = New-Object System.Data.DataSet $ ​​SqlAdapter.Fill ($ DataSet) $ SqlConnection.Close () $ DataSet.Tables [0]} 

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 9
Figure 1.5

Step 5

Please execute the following code: [Figure 1.6]

./connect3 serverlist.txt

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 10
Figure 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: HOMESQLEXPRESS
-----------------------------------
11
Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) .
Servername: HOMESQLEXPRESS
Database: XMLTest
Database: VixiaTrack
Database: test
Database: tempdb
Database: msdb
Database: model
Database: master
Database: AdventureWorks
Database: admin

Now create a similar PowerShell code, using SQL Server SMO.

Step 1

Generate code connect4.ps1 PowerShell [Figure 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}} 

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 11
Figure 1.7

Step 2

Execute the code connect4.ps1 created above [Figure 1.8]

./connect4 serverlist.txt

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 12
Figure 1.8

Result

PS C: ps> ./connect4 serverlist.txt
GAC Version Location
--- ------- --------
True v2.0.50727 C: WINDOWSassemblyGAC_MSILMicrosoft.SqlServer.Smo9 .
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: HOMESQLEXPRESS
-----------------------------------
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

Conclude

Part IV introduces how to use the PowerShell script to loop through the contents of a file and connect to different servers. This section also describes how to do the same using SQL Server SMO.

Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 13 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 14 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 15 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 16 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 17 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 18 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows Power Shell and SQL Server 2005 SMO - Part 4 Picture 19 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11