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

5 ★ | 1 Vote

May be interested

  • Create upload file with VB.NETPhoto of Create upload file with VB.NET
    uploading files is a common feature used by users to upload documents to server-based applications. this is an essential part of many applications from basic to more complex. in this article, i will show you how to add upload file to asp.net applications and create it in vb.net.
  • New points in SQL 2008 (Part 1)Photo of New points in SQL 2008 (Part 1)
    sql server 2008 will be released around february 2008, along with new versions of visual studio and windows. ctp (community technology preview) of sql 2008 is now available on the internet and you can download it from the microsoft url. in this article we will highlight some of the new features and benefits of sql server 2008
  • Query XML data from a table with XML data typePhoto of Query XML data from a table with XML data type
    this article illustrates how to create an xml schema, create a table with an xml data type, import an xml file into a table with an xml data type, query the xml file, and produce the same result as the actual result set. shown by sql transact commands.
  • Improvements in ASMPhoto of Improvements in ASM
    asm enhances the performance provided by raw disks, making it difficult to administer these disks. in addition, asm provides storage mapping policy, which can be configured at the file level instead of the drive level according to the retrieval method.
  • The ten biggest errors developers have with databasesPhoto of The ten biggest errors developers have with databases
    the software world is constantly evolving with new, more fashionable, more elegant, more functional models. it has been a long time since it products were just 'lousy' machines, having every task performed by the coders' coding functions.
  • Use and manage Database MailPhoto of Use and manage Database Mail
    sql server 2005 has a small mail system called database mail. as an improved feature in sql mail compared to earlier versions of sql server, database mail is a mail queue system. email messages are stored in an internal queue