If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

Part I of this article series discussed how to install PowerShell and how to use basic commands in PowerShell and SMO.

Part II of this series discusses more about PowerShell and its features in conjunction with SMO. If you have even a slight knowledge of languages, such as PERL, Python or C, you can see a definite similarity in the syntax when using PowerShell. In addition, it resembles Operating systems, such as UNIX, Linux, MS-DOS, etc.

Although it is very powerful, not all tasks can be achieved by PowerShell alone. However, the gaps in Power Shell can be filled by using .NET classes and Windows management Instrumentation, also known as WMI. Automation of Power Shell cmdlets can be achieved by scripting.

Retrieving system date and time using Power Shell

A simple date time value from the system can be retrieved using a simple cmdlet “date”. [Refer Fig 1.0]

Cmdlet:

date

Result:

Tuesday, June 12, 2007 8:52:27 AM

Fig 1.0

The value of system Date and Time could be retrieved using a .NET class by executing the following cmdlets. [Refer Fig 1.1]

 Fig 1.1

We can even get granular information, such as Year, Month and Day, by executing the following cmdlets. [Refer Fig 1.2]

Cmdlet: [System.DateTime]::get_now().Year [System.DateTime]::get_now().month [System.DateTime]::get_now().day Results: 2007 6 12

Fig 1.2

Use the WMI cmdlet to retrieve information about Date and Time. [Refer Fig 1.3]

Cmdlet:
get-wmiobject -Namespace root\cimv2 -Class Win32_CurrentTime
Result:
__GENUS          : 2
__CLASS          : Win32_LocalTime
__SUPERCLASS     : Win32_CurrentTime
__DYNASTY        : Win32_CurrentTime
__RELPATH        : Win32_LocalTime=@
__PROPERTY_COUNT : 10
__DERIVATION     : {Win32_CurrentTime}
__SERVER         : HOME
__NAMESPACE      : root\cimv2
__PATH           : \\HOME\root\cimv2:Win32_LocalTime=@
Day              : 12
DayOfWeek        : 2
Hour             : 9
Milliseconds     :
Minute           : 3
Month            : 6
Quarter          : 2
Second           : 0
WeekInMonth      : 3
Year             : 2007
__GENUS          : 2
__CLASS          : Win32_UTCTime
__SUPERCLASS     : Win32_CurrentTime
__DYNASTY        : Win32_CurrentTime
__RELPATH        : Win32_UTCTime=@
__PROPERTY_COUNT : 10
__DERIVATION     : {Win32_CurrentTime}
__SERVER         : HOME
__NAMESPACE      : root\cimv2
__PATH           : \\HOME\root\cimv2:Win32_UTCTime=@
Day              : 12
DayOfWeek        : 2
Hour             : 13
Milliseconds     :
Minute           : 3
Month            : 6
Quarter          : 2
Second           : 0
WeekInMonth      : 3
Year             : 2007

Fig 1.3

Power Shell also allows you to run or execute ad-hock SQL queries against your SQL 2000 or SQL 2005 databases.

We can get the value of Date and Time from SQL Server by using SQLServer Management Object and getdate() function. In this example, we are basically making a connection to a SQL Server and executing a simple getdate() function to retrieve the value of Date and Time. Execute the following command as shown below. [Refer Fig 1.4]

Fig 1.4

Cmdlets
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select getdate() as MyDate”
$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]
Results
MyDate
——
6/12/2007 9:35:18 AM

This same example can be used for any adhoc queries. Let us execute the stored procedure “sp_helpdb” as shown below.

Cmdlets
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=HOME\SQLEXPRESS;Database=master;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “sp_helpdb”
$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]
Results
name                : master
db_size             :       4.75 MB
owner               : sa
dbid                : 1
created             : Apr  8 2003
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : model
db_size             :       1.69 MB
owner               : sa
dbid                : 3
created             : Apr  8 2003
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : msdb
db_size             :       5.44 MB
owner               : sa
dbid                : 4
created             : Oct 14 2005
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics, IsFullTextEnabled
compatibility_level : 90
name                : tempdb
db_size             :       2.50 MB
owner               : sa
dbid                : 2
created             : Jun 12 2007
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatist
                      ics
compatibility_level : 90
name                : test
db_size             :       2.68 MB
owner               : HOME\MAK
dbid                : 5
created             : Jan 15 2007
status              :
compatibility_level : 90
name                : VixiaTrack
db_size             :       6.94 MB
owner               : HOME\MAK
dbid                : 6
created             : Apr 22 2007
status              :
compatibility_level : 90
name                : XMLTest
db_size             :       2.68 MB
owner               : HOME\MAK
dbid                : 7
created             : Apr 17 2007
status              : Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Col
                      lation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoClose, IsAutoCreateStatistics, IsAuto
                      UpdateStatistics, IsFullTextEnabled
compatibility_level : 90

Conclusion

Part II of this article series has illustrated the various methods (WMI, .Net classes, etc.) that you can use to retrieve information from the Windows host machine and SQL Server.