Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 20, 2007

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part II

By Muthusamy Anantha Kumar aka The MAK

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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM