Microsoft Windows PowerShell and SQL Server 2005 SMO - Part IIJune 20, 2007 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 ShellA 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
The value of system Date and Time could be retrieved using a .NET class by executing the following cmdlets. [Refer 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
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
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]
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 ConclusionPart 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
