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

Understanding Microsoft Windows power shell and SQL server 2005 smo - part 4 is easier when the main points are organized clearly. This guide brings together the essential details and practical considerations.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3

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

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 ResultOrder 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]

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: HOMEDatabase: VixiaTrackDatabase: vixiadataDatabase: vixiaDatabase: TrackEquipmentDatabase: testDatabase: tempdbDatabase: Sales2Database: SalesDatabase: pubsDatabase: NorthwindDatabase: msdbDatabase: modelDatabase: masterDatabase: Legacy_VixiaDatabase: abc3Database: abc2Database: abc

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}

Figure 1.2

Serverlist. txt HOME HOMESQLEXPRESS

Figure 1.3

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

./loop1 serverlist. txt

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]}

Figure 1.5

Step 5 Please execute the following code: [Figure 1.6]

./connect3 serverlist. txt

Figure 1.6

Result:

Details of the Server: HOME-----------------------------------19Result------Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86).Servername: HOMEDatabase: VixiaTrackDatabase: vixiadataDatabase: vixiaDatabase: TrackEquipmentDatabase: testDatabase: tempdbDatabase: Sales2Database: SalesDatabase: pubsDatabase: NorthwindDatabase: msdbDatabase: modelDatabase: masterDatabase: Legacy_VixiaDatabase: abc3Database: abc2Database: abcDetails of the Server: HOMESQLEXPRESS-----------------------------------11Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86).Servername: HOMESQLEXPRESSDatabase: XMLTestDatabase: VixiaTrackDatabase: testDatabase: tempdbDatabase: msdbDatabase: modelDatabase: masterDatabase: AdventureWorksDatabase: 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}}

Figure 1.7

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

./connect4 serverlist. txt

Figure 1.8

Result

PS C: ps>./connect4 serverlist. txtGAC Version Location--- ------- --------True v2.0.50727 C: WINDOWSassemblyGAC_MSILMicrosoft. SqlServer. Smo9.Details of the Server: HOME-----------------------------------Server Version:Server Name: 8.00.2039Database: abcDatabase: abc2Database: abc3Database: Legacy_VixiaDatabase: masterDatabase: modelDatabase: msdbDatabase: NorthwindDatabase: pubsDatabase: SalesDatabase: Sales2Database: tempdbDatabase: testDatabase: TrackEquipmentDatabase: vixiaDatabase: vixiadataDatabase: VixiaTrackDetails of the Server: HOMESQLEXPRESS-----------------------------------Server Version:Server Name: 9.00.2047.00Database: adminDatabase: AdventureWorksDatabase: masterDatabase: modelDatabase: msdbDatabase: tempdbDatabase: testDatabase: VixiaTrackDatabase: 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 PowerShell and SQL Server 2005 SMO - Part 5 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11