Using the VMWare Workstation Command Line - vmrun.exe

As the last blog post for this year (we all do need a break once in a while), I would like to focus on the use of a command-line utility for administering VMWare. The reason for this is that I need to start/stop VMWare images using the command-line whenever I do Microsoft presentations (I don't want them to know that I am no longer using Virtual PC unless they read this blog entry). Since I started using multimedia in my presentations, running multiple virtual machines simultaneously uses a lot of resources on my machine, causing my videos to stall. That's not a pleasant scene if you happen to be the audience. My solution is to use the command-line utility to start/stop the virtual images from within PowerPoint. VMware has a command-line utility called vmrun.exe (VMWare Server uses vmware-run.exe) which you can use to start/stop VMWare images. You'll find this in the VMWare Workstation folder, typically inside the C:\Program Files folder. There are a lot of parameters for this command but I will only focus on what I need. To see a list of virtual images running, you can use the list parameter as follows: vmrun list. This will show you the number of images running and the corresponding filenames for those images. This typically includes the path and the filename of the file hosting the image. Take note of this as you will use this as a value to the parameters you need to pass to the vmrun.exe command. To understand this a bit better, let's say you want to start an image named test.vmx in a folder named D:\test to start that image, you need to run this command
vmrun start D:\test\test.vmx
Just make sure that you are in the directory where vmrun.exe is located. If the location happens to include spaces between names, just enclose the path with double-quotes.To stop the image, just replace the start parameter with stop. Now, what I did with my VMWare console is that I have configured my images to run in the background when I close the console so that I no longer have to switch back and forth just to manage the image. Besides, Windows has the Remote Desktop feature that allows me to log in to the server. This keeps my machine from showing any hints of running VMWare except for the icon in the System Tray.

And if I accidentally open anything VMWare during any of my presentations, I have a very good excuse - "Technology doesn't revolve around Microsoft." This is bass_player signing off for 2007

Check if an application is installed on workstations

Last week, a friend of mine asked me if there is a way to determine if an application is installed in a workstation. He was planning to deploy IBM Lotus Sametime Connect on their network but didn't know which workstations already have it. I already have a script which audits a workstation's hardware and software so I was thinking of using this ith a little modification. He has a list of workstations in their network and he wants to use this as a reference. Here's a script which reads the text file computerList.txt containing the hostnames of workstations in your network, tries to run a PING test to see if the workstation is reachable and, if it is, runs the script to check if the application is installed. All of these generates a result which is written to a text file in CSV format so that anybody can open it in Excel to generate reports. Management loves Excel.

One thing to note is that you can change the application name to anything you wish provided you know the complete application name as stored in your Add/Remove Programs or the registry. If you want to read the hostnames from your Active Directory infrastructure, check out the script written by Matthew Jenkins (I actually validated my script against his as it is always good to have your work checked). So Raymond, this post is for you. You no longer have to go thru all 2000+ workstations in your network

Dim loopCount, directory, objFSO,objFile,objFSO2,objFile2


'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)





Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(directory & "\computerList.txt", 1)





'===LOG of servers with successful PING
strFilePath = directory & "\Results.csv"
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
' Open the file for write access.
On Error Resume Next
Set objFile2 = objFSO2.OpenTextFile(strFilePath, 2, True, 0)
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "File " & strFilePath & " cannot be opened"
Set objFSO2 = Nothing
End If
On Error GoTo 0





'Write HEADER
objFile2.WriteLine "SERVER,PING STATUS, SOFTWARE INSTALLED"


'variable to search for a specified application
strApp="IBM Lotus Sametime Connect"


Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
If Reachable(strComputer)="Success" Then
intResult = SearchApp(strComputer, strApp)
If(intResult = 1) Then
strInstalled = "INSTALLED"
ElseIf(intResult = 2) Then
strInstalled = "NONE"
ElseIf(intResult = 3) Then
strInstalled = "UNABLE TO QUERY"
End If


objFile2.WriteLine strComputer & ",SUCCESS," & strInstalled
Else
objFile2.WriteLine strComputer & "," & Reachable(strComputer) & ",N/A"
End If
Loop



objFile.Close
Set objFSO =NOTHING
Set objFile = NOTHING


objFile2.Close
Set objFSO2 =NOTHING
Set objFile2 = NOTHING


MSGBOX "Finished"





'===================================
' Function SearchApp(strComputer, sApplication)
On Error Resume Next


' Initialize some variables first
SearchApp = 2
sProgramName = ""
sProgramVersion = ""
sKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\" ' key containing uninstall info


' Attempt to connect to client's registry
Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
Set oReg = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")


' Ensure we connected ok to the client, if not just return false, it's probably not a valid Windows box
If Err.Number <> 0 Then
SearchApp = 3
Exit Function
End If


' Enumerate client registry looking for application
oReg.EnumKey HKLM, sKeyPath, arrSubKeys ' get installed programs' subkeys
For Each subKey In arrSubKeys ' get info from each installed program subkey
' attempt to get DisplayName
If(oReg.GetStringValue(HKLM, sKeyPath & subKey, "DisplayName", sProgramName) <> 0) Then
' if no DisplayName try for QuietDisplayName
oReg.GetStringValue HKLM, sKeyPath & subKey, "QuietDisplayName", sProgramName
End If


' attempt to get DisplayVersion
If(oReg.GetStringValue(HKLM, sKeyPath & subKey, "DisplayVersion", sProgramVersion) <> 0) Then
' if no DisplayName try for QuietDisplayName
oReg.GetDWORDValue HKLM, sKeyPath & subKey, "VersionMajor", sProgramVersion
End If


' If the name exists, return true
If sProgramName = sApplication Then
SearchApp = 1
Exit Function
End If
Next
End Function


'===================
Function Reachable(strComputername)


Dim wmiQuery, objWMIService, objPing, objStatus


wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"


Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)


For Each objStatus in objPing


SELECT CASE objStatus.StatusCode
CASE 0
Reachable="Success"
CASE 11001
Reachable="Buffer Too Small"
CASE 11002
Reachable="Destination Net Unreachable"
CASE 11003
Reachable="Destination Host Unreachable"
CASE 11004
Reachable="Destination Protocol Unreachable"
CASE 11005
Reachable="Destination Port Unreachable"
CASE 11006
Reachable="No Resources"
CASE 11007
Reachable="Bad Option"
CASE 11008
Reachable="Hardware Error"
CASE 11009
Reachable="Packet Too Big"
CASE 11010
Reachable="Request Timed Out"
CASE 11011
Reachable="Bad Request"
CASE 11012
Reachable="Bad Route"
CASE 11013
Reachable="TimeToLive Expired Transit"
CASE 11014
Reachable="TimeToLive Expired Reassembly"
CASE 11015
Reachable="Parameter Problem"
CASE 11016
Reachable="Source Quench"
CASE 11017
Reachable="Option Too Big"
CASE 11018
Reachable="Bad Destination"
CASE 11032
Reachable="Negotiating IPSEC"
CASE 11050
Reachable="General Failure"
END SELECT
Next
End Function

Singapore User Groups Certification Campaign

If you are in Singapore and are interested to get certified, then, read on. Microsoft Singapore in cooperation with Microsoft Learning has offered this to members of the local Singapore user groups - Windows, Office, Windows SBS, SQL Server, DotNet. If you achieve your MCP or MCTS certification between December 1, 2007 and March 31, 2008, you can get your exam fees reimbursed, courtesy of Microsoft. Visit this site for more details

Lazy DBA: Backup all your databases using a script

I have a compilation of scripts which I use on a daily basis to help me make my work a bit easier. I always mention during my presentations that the laziest people are administrators who happen to have a programming background. Here's one that I frequently use. A message on my instant messenger popped up asking me how to quickly perform backups of databases in SQL Server. I told him to write a BACKUP DATABASE command. Now, he was telling me about how much time it would take since they have like hundreds of databases. The solution: write a script. The script below performs a backup of all the databases on a SQL Server 2000 instance and dumps it in a local folder.

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''F\BACKUP\'+@DBNAME+'.BAK'' WITH INIT, STATS=10'
PRINT "==========================================="
EXEC (@SQL)
PRINT "Backup for database " + @DBNAME + " has been created"
SELECT @IDENT=min(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
END

This excludes the tempdb, Northwind and Pubs databases should you have it in your instance. Restoring is, of course, a different story. You need to start with the system databases (master and msdb, in my case) before you can restore the user databases. In a future blog post, I'll have a script to read the backups generated by this script and restore them all. See how lazy I can be?

Say hello to VMWare

What can I say, I had the opportunity to work on VMWare Workstation because of an not-for-resale (NFR) license provided for Microsoft MVPs. Having a Windows XP laptop with very limited RAM is a limitation I have to contend with everytime I do presentations. Imagine running a virtual image hosting a domain controller, DHCP, DNS, IIS, SQL Server 2005 and System Center Configuration Manager 2007 with at least two or three virtual clients. My laptop goes haywire after firing up all of my virtual images. I think I'll stick to VMWare Workstation while waiting for a more powerful laptop.

One more thing, I managed to configure my virtual network with a server and workstation to access the Internet thru NAT. Since I am not allowed to add workstations on our local network, I cannot use the host machine's network card to access the Internet. I configured the network card of the VMWare image to use NAT, assigned a static IP which is in the same subnet as the VMWare NAT address on my host and pointed its DNS to the same IP as well. This made my VMWare image access the Internet thru NAT using a static IP. Dynamic IP would be a lot easier as the VMWare DHCP will assign an IP to my image but that was not an option for me since I am working with a server, which needs a static IP. After making sure that my server can access the Internet, I configured routing and remote access on my virtual Windows Server 2003 so I route the traffic from my other subnet to the IP I used to access the NAT. This way, my clients can access the Internet but are only accessible thru my virtual network,meaning even my host machine cannot access my virtual clients. Quite cool, huh.

For my first test, I did my SQL Server 2008 Declarative Management Framework session for the Singapore SQL Server User Group all on VMWare Workstation. And the audience didn't even notice I was using a non-Microsoft product. I'll post the details of my session in a separate entry

Goodbye Orcas, Hello RTM

I haven't been up to date with Visual Studio 2008 now that it already has RTMed. Since I have some spare time to download the RTM version, I've decided to uninstall my Beta 1 installation of Visual Studio Orcas. In previous versions like Visual Studio 2005, this was a total pain until Microsoft decided to release those cleanup utility. For Visual Studio 2008, Microsoft came up with a documentation on how to uninstall the previous builds before installing the RTM version (unfortunately, I've installed Beta 1 on my "work" station which I rarely do). The only issue I have encountered is that the uninstall process screwed up my Microsoft Outlook 2003, probably because of the Visual Studio Tools for Office Runtime. I ended up fixing my Microsoft Office 2003 installation. I'll wait until I finish downloading the bits then off I go to install Visual Studio 2008 (I still have to be content with SQL Server 2008 CTP for the mean time)

Change the Local Administrator password on all your domain computers

Imagine this - 200 servers and 1,500 workstations. How would you change the local Administrator password (or any other account which you use to administer the local machine)? Being the lazy guy as I always have been, I don't want to do things which would be repetitive so I wrote a script. This uses a list of servers and workstations stored in a text file named computerList.txt (you can use either hostnames or IP addresses although I prefer IP) and generates a CSV file which I can use as a report.

Dim loopCount, directory, objFSO,objFile,objFSO2,objFile2

'Gets the directory where our script is running from
directory = CreateObject("Scripting.FileSystemObject").GetParentFolderName(Wscript.ScriptFullName)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(directory & "\computerList.txt", 1)

'===LOG of servers with successful PING
strFilePath = directory & "\serversPING.csv"
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
' Open the file for write access.
On Error Resume Next
Set objFile2 = objFSO2.OpenTextFile(strFilePath, 2, True, 0)
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "File " & strFilePath & " cannot be opened"
Set objFSO2 = Nothing
End If
On Error GoTo 0

'Write HEADER
objFile2.WriteLine "SERVER,REACHABLE,PASSWORD CHANGED"

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine

If Reachable(strComputer) Then
strReachable = "REACHABLE"
strPasswordChanged = "SUCCESSFUL"
Call SetPassword(strComputer)
Else
strReachable = "UNREACHABLE"
strPasswordChanged = "FAILURE"
End If

objFile2.WriteLine strComputer & "," & strReachable & "," & strPasswordChanged

Loop

objFile.Close
Set objFSO =NOTHING
Set objFile = NOTHING

objFile2.Close
Set objFSO2 =NOTHING
Set objFile2 = NOTHING

MSGBOX "Finished"

'===============================
Function Reachable(strComputer)
' On Error Resume Next

Dim wmiQuery, objWMIService, objPing, objStatus

wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set objPing = objWMIService.ExecQuery(wmiQuery)

For Each objStatus in objPing
If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
Reachable = False 'if computer is unreacable, return false
Else
Reachable = True 'if computer is reachable, return true
End If
Next
End Function

'===================================
Function SetPassword(strComputer)
strComputer = strComputer
Set objUser = GetObject("WinNT://" & strComputer & "/Administrator, user")
objUser.SetPassword "T3$tP@$$w0rd"
objUser.SetInfo

End Function

Imagine how much time can be saved if you needed to do this ever 45 days. I'll work on something which reads Active Directory for the list of all computers and servers joined in the domain so one does not need to create the computerList.txt file

Managing the Windows Internal Database (SQL Server 2005 Embedded Edition)

As a follow up to the entry on Windows Internal Database, I keep getting questions on how to manage this if in case you do not want to remove it. The first time I heard about this, I thought Microsoft may have missed out on informing me about the SQL Server 2005 Embedded Edition as this does not appear in the list of Editions for SQL Server 2005. This is the replacement for WMSDE which a few of the Microsoft products - Windows Sharepoint Services 3.0 and Windows Software Update Services 3.0, for example- use as a backend database. Most of the questions I get are administration related like how to shrink the database files, move them on a different partition, etc. By default, there is no tool available to manage the database hosted in this instance (MICROSOFT#SSEE). But you can use the available SQL Server 2005 tools to do administration and maintenance. The simplest there is will be to install SQL Server 2005 Management Studio Express. This is an available download from the Microsoft Download Center. You just have to register this instance on the Management Studio Express to be able to administer it. One thing to remember is that during the registration process, you have to specify the Network Protocol option in the New Server Registration window to be Named Pipes as this is the one being used by this instance. Once connected, you can now start managing the databases running on this instance. Another way is to use the sqlcmd tool. This is the command-line utility of choice for SQL Server 2005, although you can still use the osql utility. You need to download the SQL Server 2005 Native Client and SQL Server 2005 Command Line Query Utility from the Microsoft Download Center. You need to install the Native Client first before installing the Command Line Query Utility. Once installed, you can run sqlcmd.exe from the command line. This is typically installed in this directory

C:\Program Files\Microsoft SQL Server\90\Tools\binn

To connect to the Windows Internal Database instance (MICROSOFT##SSEE), run this command

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE \sql\query –E

Once connected, you can now execute those TSQL scripts that you are familiar with to manage and administer databases in this instance.

NOTE: This post was originally from my old blog site

Uninstalling SQL Server 2005 Embedded Edition

I thought I was just seeing things when I saw this in the Services applet - SQL Server 2005 Embedded Edition(MICROSOFT##SSEE). This is the version of SQL Server 2005 that Windows Sharepoint Services 3.0 installs on your server if you simply choose all the defaults. The problem with this is that there is no way for you to manage it from the SQL Server tools we are all familiar with (I tried it with Management Studio and sqlcmd but with no luck as it just throws an error saying that the server instance does not exist). Besides, if you want to scale up your database, you would have to migrate it to either Standard or Enterprise Edition. This renders this instance useless. Most of us will simply recommend uninstalling this version. The problem is, you won't see this in your Add/Remove Programs applet in Windows. It's an invisible instance that the only way to uninstall this is to uninstall Windows Sharepoint Services as well (unless you want to go through the process, I suggest not doing a complete reinstall). So how do you uninstall SQL Server 2005 Embedded Edition? I found it through this website.

1. Start Registry Editor, and then locate the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

2. In the left pane, click each GUID. For each GUID that you click, look for a display name in the right pane that matches “Microsoft SQL 2005 Embedded Edition…”.

3. When you see a display name that matches the name, copy the value of the Key named “UninstallString”
4. Open a Command-Window (Start->run->cmd)
5. Paste the Copied string. It should be something like this: MsiExec.exe /X{BDD79957-5801-4A2D-B09E-852E7FA64D01}
6. Append “ CALLERID=OCSETUP.EXE” at the end of the MsiExec.exe eg “MsiExec.exe /X{BDD79957-5801-4A2D-B09E-852E7FA64D01} CALLERID=OCSETUP.EXE”

7. Run the command. This will go through the process of uninstalling SQL Server 2005 Embedded Edition

8. Reboot.

To verify, you can check the Services applet and see if the SQL Server 2005 Embedded Edition (MICROSOFT##SSEE) service is no longer there

What happened to my WSUS 2.0? November 12, 2007

This problem has been on a lot of newsgroups and forums available on the Internet and it has something to do with WSUS Administration Console screwing up.

Apparently, a category for a product was renamed cuasing it to introduce restricted characters in the form of double-quotes in the backend database (the product category happened to be codenamed Nitrogen).

This has caused the application to throw an error, thus, making the WSUS Administration Console inaccessible (see this Microsoft WSUS blog entry for more details). A workaround has been provided by the WSUS Team that deals with this problem but has something to do with updating or modifying some records in the SUSDB database. Here's my version of one of those SQL scripts which you need to run from your command-line

osql.exe –S\WSUS –E –Q”USE SUSDB; BEGIN TRAN; EXEC dbo.spStartCatalogSync; COMMIT TRAN”

Make sure to replace with your server hostname before running this script. Another version of the workaround can be found from the SANS Internet Storm Center website

OSQL -S \WSUS -E

1> USE SUSDB

2> GO

1> Update tbPrecomputedCategoryLocalizedProperty Set Title = Replace(Title, '"', '') Where Title like '%"%'

2> GO

1> Update tbPreComputedLocalizedProperty Set Title = Replace(Title, '"', '') Where Title like '%"%'

2>GO

1>QUIT

One thing to highlight here for those who are not comfortable with Transact-SQL is that we are trying to update the Title field of the tbPrecomputedCategoryLocalizedProperty and tbPrecomputedLocalizedProperty tables and replacing the double-quotes with single-quotes using the Replace function.

What I don't understand is why Microsoft tried push the update for public consumption if it's still in Beta (try reading the description of the product to find out) although they did promise that it was pulled out of their servers. I'm still keeping my fingers crossed

Table Valued Parameters in SQL Server 2008

As developers, there is always a need to pass data structures in one form or another in any programming language. TSQL is no exemption. This has been addressed by some workarounds as OPENXML in SQL Server 2000 where you can pass data around as VARCHAR data type. in SQL Server 2005, you have the XML data type where you can store data in XML form in a database column and use XPath with XQuery to manipulate the data. Both of which can, indeed, help pass data structures but still needs a lot of work to make it happen. Another approach is to create a temporary table on demand, populate the table before actually using the data in a stored procedure or function. SQL Server 2008 offers you the capabilities of passing a table as a parameter in a stored procedure and a function. Imagine populating a table on a client application and pass the table as a parameter to the backend database at one shot, thereby eliminating a lot of roundtrips, not to mention a couple of lines of codes. To understand how it works, let's have a look at a sample code. First, I'll create a very simple table named TableTVP1 which will be our target table.

--create sample table
CREATE TABLE [dbo].[TableTVP1]
(rowID int)
GO

The first step in understanding the concept of a table-valued parameter is to create a table-type; a new parameter which is of type table.

--Create TABLE type
CREATE TYPE tvp_TableTVP1 AS TABLE
(rowID int)
GO

The good thing about creating a table-type is that you can re-use it similar to re-using a user-defined data type. This means, you can use it to access any table with similar structure using the table-type(imagine having tables like Employees, Customers, Partners, etc. having similar table structures). After we have created the table-type, we can now use it it a stored procedure or function. The code below illustrates the use of the table-type we've just created

--Setup stored procedure to accept the new TABLE type
CREATE PROCEDURE usp_tvpInsert (@tableparam tvp_TableTVP1 READONLY)
AS
INSERT TableTVP1
SELECT * FROM @tableparam
GO

Notice how we used the table-type tvp_Table1TVP1 as a type to define the parameter @tableparam. It's just like any parameter you pass to a stored procedure or function except that it is defined as a table. The READONLY attribbute passed simply tells us that a table-type passed as a parameter cannot be modified and no DML operations are permitted unlike when dealing with temporary tables. This means you need to do the data manipulation outside of this stored procedure or function before you can pass the table-type. Now that we have both the table-type and the stored procedure, let's see how it works.

--Use the stored procedure
DECLARE @tableparam tvp_TableTVP1
DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=1000)
BEGIN
INSERT @tableparam VALUES (@i)
SET @i = @i + 1
END

EXEC usp_tvpInsert @tableParam

The code simply inserts records from 1 to 1000 on a table-type named @tableparam and passed it to the stored procedure. Imagine doing this in your client application and passing it one-time as a parameter in a stored procedure. It reduces the number of server roundtrips and improves response time aside from benefitting from being able to pass a table as a parameter. Check out my SQL Server 2008 videos at BlogCastRepository.com where a .NET application is also available for download as a sample that uses TVP

Identify password expiration in Active Directory

If you've got a huge amount of mobile workforce - people who are always on the go, it would be very difficult to track whether or not they change their passwords. This is very important if your users still access your network thru VPN and use their Active Directory credentials to log on. I see a lot of people who rarely log on to their domain, probably once in a month since they are always on the road, probably doing sales calls or out of the country. I wrote a script to check Active Directory for all the user accounts, check for their password expiration, and send them an email if their passwords are set to expire in less than 14 days. In a typical environment, the users will get a prompt if they log in to the domain on a regular basis. This solves the "not-so-typical" case of users rarely logging on to a domain.

Const ADS_UF_PASSWD_CANT_CHANGE = &H40
Const ADS_UF_DONT_EXPIRE_PASSWD = &H10000
Const ADS_UF_ACCOUNTDISABLE = &H02

Dim strFilePath, objFSO, objFile, adoConnection, adoCommand
Dim objRootDSE, strDNSDomain, strFilter, strQuery, adoRecordset
Dim strDN, objShell, lngBiasKey, lngBias, blnPwdExpire,blnAccountDisabled
Dim objDate, dtmPwdLastSet, lngFlag, k, oDomain, maxPwdAge, numDays,whenPasswordExpires, strEmailMessage

'====================================
'Script to change a filename using timestamps
Dim strMonth, strDay
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())

if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if

if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if
'===================================

strFilePath = "D:\users_DOMAIN_" & DatePart("yyyy",Now()) & strMonth & strDay & ".txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open the file for write access.
On Error Resume Next
Set objFile = objFSO.OpenTextFile(strFilePath, 2, True, 0)
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "File " & strFilePath & " cannot be opened"
Set objFSO = Nothing
Wscript.Quit(1)
End If
On Error GoTo 0

' Obtain local time zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
& "TimeZoneInformation\ActiveTimeBias")
If (UCase(TypeName(lngBiasKey)) = "LONG") Then
lngBias = lngBiasKey
ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then
lngBias = 0
For k = 0 To UBound(lngBiasKey)
lngBias = lngBias + (lngBiasKey(k) * 256^k)
Next
End If

' Use ADO to search the domain for all users.
Set adoConnection = CreateObject("ADODB.Connection")
Set adoCommand = CreateObject("ADODB.Command")
adoConnection.Provider = "ADsDSOOBject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")

' Filter to retrieve all user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Filter to retrieve all computer objects.
' strFilter = "(objectCategory=computer)"

strQuery = ";" & strFilter _
& ";displayName,pwdLastSet,userAccountControl,mail;subtree"

adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Iterate thru the users collection in Active Directory
objFile.WriteLine "DISPLAY NAME , PASSWORD EXPIRES, ACCOUNT DISABLED, PASSWORD LAST SET , EMAIL, PASSWORD EXPIRES, NUMBER OF DAYS"

Set oDomain = GetObject("LDAP://dc=DOMAIN,dc=local")
Set maxPwdAge = oDomain.Get("maxPwdAge")

numDays = ((maxPwdAge.HighPart * 2 ^ 32) + maxPwdAge.LowPart) / -864000000000

Set adoRecordset = adoCommand.Execute
Do Until adoRecordset.EOF

Set objDate = adoRecordset.Fields("pwdLastSet").Value

lngFlag = adoRecordset.Fields("userAccountControl").Value
blnPwdExpire = True
dtmPwdLastSet = Integer8Date(objDate, lngBias)
whenPasswordExpires = DateAdd("d", numDays, dtmPwdLastSet)

If ((lngFlag And ADS_UF_PASSWD_CANT_CHANGE) <> 0) Then
blnPwdExpire = False
End If

If ((lngFlag And ADS_UF_DONT_EXPIRE_PASSWD) <> 0) Then
blnPwdExpire = False
End If

If (lngFlag And ADS_UF_ACCOUNTDISABLE) <> 0 Then
blnAccountDisabled=True
Else
blnAccountDisabled=False

If IsNull(adoRecordset.Fields("mail").Value) or IsEmpty(adoRecordset.Fields("mail").Value) Then

Else
'check if password expires
If blnPwdExpire = True Then
If DateDiff("d", Now, whenPasswordExpires) <=14 AND DateDiff("d", Now, whenPasswordExpires) >=0 Then
strEmailMessage="1" 'password will expire in less than 14 days
objFile.WriteLine adoRecordset.Fields("displayName").Value & "," & blnPwdExpire & " , " & blnAccountDisabled & " , " & dtmPwdLastSet & " , " & adoRecordset.Fields("mail").Value & "," & whenPasswordExpires & "," & DateDiff("d", Now, whenPasswordExpires)
Call sendEmail(adoRecordset.Fields("mail").Value,FormatDateTime(whenPasswordExpires,2),strEmailMessage)
ElseIf DateDiff("d", Now, whenPasswordExpires) <0 Then
strEmailMessage="0" 'password has already expired
objFile.WriteLine adoRecordset.Fields("displayName").Value & "," & blnPwdExpire & " , " & blnAccountDisabled & " , " & dtmPwdLastSet & " , " & adoRecordset.Fields("mail").Value & "," & whenPasswordExpires & "," & DateDiff("d", Now, whenPasswordExpires)
Call sendEmail(adoRecordset.Fields("mail").Value,FormatDateTime(whenPasswordExpires,2),strEmailMessage)
End If
End If

End if
End If

adoRecordset.MoveNext
Loop

adoRecordset.Close

' Clean up.
objFile.Close
adoConnection.Close
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing
Set adoConnection = Nothing
Set adoCommand = Nothing
Set objRootDSE = Nothing
Set adoRecordset = Nothing

Wscript.Echo "Done"

'=============================
'Function -Integer8 attribute function courtesy of Richard Mueller - http://www.rlmueller.net/Integer8Attributes.htm
Function Integer8Date(ByVal objDate, ByVal lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
Dim lngAdjust, lngDate, lngHigh, lngLow
lngAdjust = lngBias
lngHigh = objDate.HighPart
lngLow = objdate.LowPart
' Account for bug in IADslargeInteger property methods.
If (lngLow < 0) Then
lngHigh = lngHigh + 1
End If
If (lngHigh = 0) And (lngLow = 0) Then
lngAdjust = 0
End If
lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
+ lngLow) / 600000000 - lngAdjust) / 1440
Integer8Date = CDate(lngDate)
End Function

'=============================
'Send Email function
Sub sendEmail(strEmail, expirationDate, msgFlag)
'Accept input parameters
Dim email
Dim expirationDate
Dim strMessage

email= strEmail
expirationDate= expirationDate
strMessage= msgFlag

If strMessage=1 then
strMessage=" will expire on "
strMessage2= "Password Expiration"
ElseIf strMessage=0 then
strMessage=" has already expired last "
strMessage2= "Expired Password"
End If

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Message Alert from Domain Administrator: " & strMessage2
objMessage.From = "admin@domain.local"
objMessage.To = email
objMessage.TextBody = "Your domain password " & strMessage & " " & FormatDateTime(expirationDate,1) & ". This password notification notice is being sent once a week " & vbCrLF & vbCrLf & "Please change your password. "& vbCrlf & vbCrlf & vbCrlf & vbCrlf & "Domain Administrator"

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.domain.local"

'Server port number(typically 25)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

objMessage.Send
Set objMessage = Nothing
End Sub

The use of ADO was actually not my preference since it requires you to use an additional layer just to connect to ADSI. My original script was actually using the WinNT provider to access Active Directory. But upon further research, I found out that the WinNT provider does not have pointers to access the email attribute in the user object. I need this attribute to send emails to those users whose passwords will be expiring. This prompted me to re-write my script to use the LDAP provider and ADO.

I have used the following as reference to write this script. Feel free to use it and customize in a way that suites your requirement.

http://www.rlmueller.net/
http://support.microsoft.com/kb/323750
http://msdn2.microsoft.com/en-us/library/aa772170.aspx

using WMI and VBScript to audit your workstations

Auditing hardware and software for all machines in your domain can be time consuming. For small organizations, it may be a bit manageable provided that you already have a checklist of items that you need to look at for auditing purposes. CPU, disk, RAM, OS, service pack versions, etc. are just a few of those things you would like to take into account when doing auditing and inventory. For larger organizations, this could be a big challenge. Since I work for an organization that provides IT services for clients, we are required to maintain server information for auditing and inventory purposes. Imagine me doing this for almost 200+ Windows 2000 and Windows 2003 servers in multiple domains, not to mention Windows XP workstations. Being a lazy guy as I am, I wrote a script to simply automate this task. It uses VBScript and WMI to retrieve hardware, software and operating system information from computers in the domain. This requires administrative rights on the machine where this script is executed. It generates a text file (with filename servername_yyyymmdd_Audit.txt) which contains the information retrieved by the script. This is my list of information, you can always generate a lot more information by referring to the WMI SDK. Simply replace the value in the strComputer variable to the name/IP address of the computer you wish to audit

On Error Resume Next

Const HKEY_LOCAL_MACHINE = &H80000002

'change this value to the IP address or hostname of the machine you need to audit
strIPvalue = "."

CALL GenerateReport(strIPvalue)

WScript.Echo "Inventory Complete "


'=================================================================================
'SUB-ROUTINE GenerateReport
SUB GenerateReport(strIPvalue)

'Script to change a filename using timestamps
strPath = "C:\" 'Change the path to appropriate value
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())

if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if


if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if


strFileName = DatePart("yyyy",Now()) & strMonth & strDay
strFileName = Replace(strFileName,":","")
'=================================================================================

'Variable Declarations
Const ForAppending = 8

'===============================================================================
'Main Body
On Error Resume Next


'CompName
strComputer = strIPvalue
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
'===============================================================================

'================================================================
'For INTERNET EXPLORER
Dim strIE
Set objWMIService2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2\Applications\MicrosoftIE")
Set colIESettings = objWMIService2.ExecQuery("Select * from MicrosoftIE_Summary")
For Each strIESetting in colIESettings
strIE= " INTERNET EXPLORER: " & strIESetting.Name & " v" & strIESetting.Version & VBCRLF
Next


'Get Operation System & Processor Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem in colItems
CompName = objItem.SystemName
Next

Set objFSO = CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(strPath & CompName & "_" & strFileName & "_Audit.txt") then
WScript.Quit
end if

'Set the file location to collect the data
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(strPath & CompName & "_" & strFileName & "_Audit.txt", ForAppending, True)

''==============================================================
'Print HEADER
objTextFile.Write "================================================================" & VBCRLF & VBCRLF
objTextFile.Write " SERVER RESOURCE AUDIT REPORT " & VBCRLF
objTextFile.Write " DATE: " & FormatDateTime(Now(),1) & " " & VBCRLF
objTextFile.Write " TIME: " & FormatDateTime(Now(),3) & " " & VBCRLF & VBCRLF
objTextFile.Write "================================================================" & VBCRLF & VBCRLF & VBCRLF & VBCRLF & VBCRLF


objTextFile.Write "COMPUTER" & VBCRLF
'==============================================================
'Get OPERATING SYSTEM & Processor Information
objTextFile.Write " COMPUTER NAME: " & CompName & VBCRLF

Set colItems = objWMIService.ExecQuery("Select * from Win32_Processor",,48)
For Each objItem in colItems
objTextFile.Write " PROCESSOR: " & objItem.Name & VBCRLF
Next

Set colProcs = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")

For Each objItem in colProcs
objTextFile.Write " NUMBER OF PROCESSORS: " & objItem.NumberOfProcessors & VBCRLF & VBCRLF
Next


'================================================================
'Get DOMAIN NAME information
Set colItems = objWMIService.ExecQuery("Select * from Win32_NTDomain")

For Each objItem in colItems
objTextFile.Write " DOMAIN NAME: " & objItem.DomainName & VBCRLF
Next

'================================================================
'Get OS Information
Set colSettings = objWMIService.ExecQuery("SELECT * FROM Win32_OperatingSystem")
For Each objOperatingSystem in colSettings
objTextFile.Write " OPERATING SYSTEM: " & objOperatingSystem.Name & VBCRLF
objTextFile.Write " VERSION: " & objOperatingSystem.Version & VBCRLF
objTextFile.Write " SERVICE PACK: " & objOperatingSystem.ServicePackMajorVersion & "." & objOperatingSystem.ServicePackMinorVersion & VBCRLF
Next
objTextFile.Write strIE & VBCRLF & VBCRLF & VBCRLF & VBCRLF





objTextFile.Write "MOTHERBOARD" & VBCRLF

'===============================================================
'Get Main Board Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_BaseBoard",,48)
For Each objItem in colItems
objTextFile.Write " MAINBOARD MANUFACTURER: " & objItem.Manufacturer & VBCRLF
objTextFile.Write " MAINBOARD PRODUCT: " & objItem.Product & VBCRLF
Next



'================================================================
'Get BIOS Information
Set colItems = objWMIService.ExecQuery("Select * from Win32_BIOS",,48)
For Each objItem in colItems
objTextFile.Write " BIOS MANUFACTURER: " & objItem.Manufacturer & VBCRLF
objTextFile.Write " BIOS VERSION: " & objItem.Version & VBCRLF & VBCRLF & VBCRLF & VBCRLF & VBCRLF
Next

objTextFile.Write "MEMORY" & VBCRLF

'===================================================================
'Get Total Physical memory
Set colSettings = objWMIService.ExecQuery("Select * from Win32_ComputerSystem")
For Each objComputer in colSettings
objTextFile.Write " TOTAL PHYSICAL RAM: " & Round((objComputer.TotalPhysicalMemory/1000000000),4) & " GB" & VBCRLF
Next

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "PARTITIONS" & VBCRLF

'===================================================================
'Get Logical Disk Size and Partition Information
Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk Where DriveType = 3")
For Each objDisk in colDisks
intFreeSpace = objDisk.FreeSpace
intTotalSpace = objDisk.Size
pctFreeSpace = intFreeSpace / intTotalSpace
objTextFile.Write " DISK " & objDisk.DeviceID & " (" & objDisk.FileSystem & ") " & Round((objDisk.Size/1000000000),4) & " GB ("& Round((intFreeSpace/1000000000)*1.024,4) & " GB Free Space)" & VBCRLF
Next

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "NETWORK" & VBCRLF

'====================================================================
'Get NETWORK ADAPTERS information
Dim strIP, strSubnet, strDescription

Set colNicConfigs = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")

For Each objNicConfig In colNicConfigs
'Assign description values to variable
strDescription=objNicConfig.Description

For Each strIPAddress In objNicConfig.IPAddress
'Assign IP Address to variable
strIP=strIPAddress

For Each strIPSubnet In objNicConfig.IPSubnet
'Assign Subnet to variable
strSubnet = strIPSubnet
Next

objTextFile.Write " NETWORK ADAPTER: " & strDescription & VBCRLF
objTextFile.Write " IP ADDRESS: " & strIP & VBCRLF
objTextFile.Write " SUBNET MASK: " & strSubnet & VBCRLF & VBCRLF

Next

Next

Set colNicConfigs =NOTHING


'============================================================

objTextFile.Write " " & VBCRLF & VBCRLF & VBCRLF & VBCRLF & "APPLICATION" & VBCRLF

Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall"
objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

For Each subkey In arrSubKeys
strSubKeyPath = strKeyPath & "\" & subkey

strString = "DisplayName"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strSubKeyPath, strString, strDisplayName

strString = "DisplayVersion"
objReg.GetStringValue HKEY_LOCAL_MACHINE, strSubKeyPath, strString, strDisplayVersion

strDisplayName=Trim(strDisplayName)
strDisplayVersion=Trim(strDisplayVersion)
If strDisplayName <> "" And strDisplayVersion <> "" Then
objTextFile.Write " " & strDisplayName & " " & strDisplayVersion & VBCRLF
End If
Next


'===========================================



'Close text file after writing logs

objTextFile.Write VbCrLf
objTextFile.Close

'Clean Up

SET colIESettings=NOTHING
SET colItems=NOTHING
SET colSettings=NOTHING
SET colDisks=NOTHING
SET AdapterSet=NOTHING
SET objWMIService=NOTHING
SET objWMIService2=NOTHING
SET objFSO=NOTHING
SET objTextFile=NOTHING


'===================================================================
END SUB



Function HostOnline(strComputername)


Set sTempFolder = objFso.GetSpecialFolder(TEMPFOLDER)
sTempFile = objFso.GetTempName
sTempFile = sTempFolder & "\" & sTempFile

objShell.Run "cmd /c ping -n 2 -l 8 " & strComputername & ">" & sTempFile,0,True

Set oFile = objFso.GetFile(sTempFile)
set oTS = oFile.OpenAsTextStream(ForReading)
do while oTS.AtEndOfStream <> True
sReturn = oTS.ReadLine
if instr(sReturn, "Reply")>0 then
HostOnline = True
Exit Do
End If
Loop

ots.Close
oFile.delete
End Function

If you are dealing with an organization with more than 50 computers and servers, it would still be tiresome to manually execute this script on each machine/server. A better way to do it is to have an Excel spreadsheet that contains the machine names/IP addresses of all the computers in your domain. read through the list and generate the text files based on that list. In my next blog entry, I will start with reading an Excel spreadsheet using VBScript and continue on to incorporate this script.

Making a fool of your applications - File System

An understanding of how your applications work will make it a bit easy for you to fix problems when they arise. One common scenario I have seen in most applications is the concept of application logging - creating logs using either the old-fashioned log files or storing the data in a database. Logging helps application developers troubleshoot application-related problems in production environments when the need arise. I've had my shares of creating application logs in the past where I use text files to store activities happening in the application. There's a downside in using this concept. One, you are introducing additional overhead in your application since you have to do an additional step, not to mention the I/O necessary to write the log in the file system or the database. Another downside is log maintenance. If you do not maintain the logs, it wouldn't take long before they fill up your disk, causing your application to fail. There are ways to work around this. You can toggle logging and only turn it on when needed. Another approach is to create a default log maintenance procedure in the application itself like truncating logs or deleting log files older than a specific date. Maintenance will be a terrible headache if not included as a part of the logging mechanism.

I have had the opportunity to deal with such a case. I had an encounter with an application which logs every transaction by creating XML files. XML is a great way to store data. But what I have seen for the past few years is that the use of XML has been misunderstood as something to replace a relational database. This misunderstanding of its purpose has caused a lot of problems particularly when it comes to performance. You see, in order for you to read the data in the XML file, you have to load it in memory before you can even do those methods as parsing using XPath and XQuery. Imagine doing this to load a million XML files. My problem was to delete the log files stored as XML. I couldn't just delete them since they have increased in number that simply running Windows Explorer has caused my session to hang. My next step is to delete the folder containing the log files. But it's not as easy as that. The folder containing the logs is being locked by the application which is typical of all applications creating logs. To work around this problem, I had to find out what application or service is, stop it the service, rename the folder containing the logs, create a new folder with the same name as that of the old log folder, restart the service and, then, delete the old folder. The application will still see the logs folder except that now it's a new folder but with the same name. This made sure that I can still do maintenance by deleting the logs folder while making sure that application downtime is kept at a minimum. Bottomline still remains, we people are indeed smarter than these machines.

Common Table Expressions in SQL Server 2005

I initially wrote this article for www.adminprep.com but apparently, I myself am having difficulty trying to access the site every now and then so I decided to mirror the content in my blog.

Have you ever wished that Microsoft created a feature in SQL Server 2000 that combines the capabilities of views and derived tables? How about writing recursive queries in T-SQL that does not need to be very taxing? With SQL Server 2005 Common Table Expressions, you can do both with ease. In their simple form, you can think of CTEs as an improved version of derived tables that more closely resemble a non-persistent type of view. Look at CTEs as your derived tables in SQL Server 2000. A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries simpler, like recursive queries. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let’s first take a look at the syntax to create it in SQL Server 2005.

Syntax

In general form a CTE has the following syntax:

WITH cte_alias(column_aliases)
AS
(
cte_query
)
SELECT *
FROM cte_alias

You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH which usually defines the derived table based on the query definition; write the body of the CTE; and refer to it from the outer query.

To put this in the right perspective, let’s come up with a very simple example. Using the Northwind database, we want to display the employee details along with a column that displays the number of orders that the employee has written. This is done by creating a table on the fly that summarizes this information from the orders table. We then join with this table in the INNER JOIN clause based on employee ID.

WITH Count_Orders(employee_ID, orderCount)
AS
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
SELECT employeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN Count_Orders
ON Count_Orders.employee_ID = E.employeeID

Now, a lot of people might say that this is quite simple. You can do this in SQL Server 2000 using the concept of derived tables or temporary tables. If you were to write the same query in SQL Server 2000, this is how it would look like.

SELECT EmployeeID, Firstname, Lastname, orderCount
FROM Employees E INNER JOIN
(
SELECT employeeID, COUNT(OrderID)
FROM Orders
GROUP BY employeeID
)
AS Count_Orders (employee_ID, order_Count)
ON Count_Orders.employee_ID = E.emplyeeID

One might argue that these two don’t have that much of a difference. Now, imagine that you need to refer to the same derived table within the query. You would have to repeat the same definition, create an alias for it before you can use it again. As you increase the number of references, your code becomes pretty long but repetitive. With CTEs, you no longer have to do these things again and your code becomes easier to read. You can define multiple CTEs and incrementally build on the earlier CTEs or define new results that are then used later on.

Recursive Queries

Whenever a CTE refers to itself, it is considered to be a recursive query. Recursive CTEs are constructed from at least two queries. One is a non-recursive query, which is also referred to as the anchor member. The other is the recursive query, also referred to as the recursive member. These queries are separated by the UNION ALL operator.

Let’s take a look at a simplified generic form of a recursive CTE.

WITH RecursiveCTE(column_list)
AS
(
-- Anchor Member:
-- SELECT query that does not refer to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
...
UNION ALL
-- Recursive Member
-- SELECT query that refers to RecursiveCTE
SELECT ...
FROM some_table(s)_or_view(s)
JOIN RecursiveCTE
...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

Let’s extend the example we used to demonstrate how to write recursive CTEs. Looking at the Employees table in the Northwind database, we see that a particular employee reports to another employee. One question we can come up with is, “Who reports to whom?” The Employees table of the Northwind database is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID. Thus, we can create a query to answer our question. A sample query using CTE will look something like this.

WITH Managers AS
(
SELECT EmployeeID, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
SELECT e.employeeID, e.ReportsTo
FROM Employees e INNER JOIN Managers m ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

The sample query contains the elements that a recursive CTE must contain. What’s more is that the code is a lot more readable. To create a similar yet non-recursive query that produces the same result in SQL Server 2000, you might come up with something similar to this code:

DECLARE @rowsAdded int
--table variable to hold accumulated results
DECLARE @managers table
(EmpID int, MgrID int, processed int default(0))

--initialize @managers who do not have managers
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded=@@rowcount

--do this while new employees are added in the previous iteration
WHILE @rowsAdded > 0
BEGIN
--mark employee records going to be found in this iteration with
--processed=1
UPDATE @managers SET processed=1 WHERE processed=0

--insert employees who report to employees not yet processed
INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees e
INNER JOIN @managers r ON e.ReportsTo = r.EmpID
WHERE ReportsTo <> EmployeeID and r.processed = 1

SET @rowsAdded = @@rowcount

--mark employee records found in this iteration as processed
UPDATE @managers SET processed=2 WHERE processed=1
END

SELECT * FROM @managers

The first thing you will notice is the codes were quite few when using CTEs compared to the usual T-SQL query you will have to create in SQL Server 2000. This enables the developers to write complex queries with ease. You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE. To use it in the previous example

SELECT * FROM Managers OPTION (MAXRECURSION 4)

Given the example above, hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. Common Table Expression is just one of those T-SQL enhancements available for SQL Server 2005. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused. As our data requirements become more complex, we need the proper tools to deal with them and the new generation of T-SQL is just the right tools that we need. In my next article, I will introduce more T-SQL enhancements in SQL Server 2008 which is similar to how to address problems like this using CTEs. SQL Server 2008 has introduced a new data type called heirarchyid which I will cover soon

Fancy up your reports - SQL Server 2005 Reporting Services:alternating colors on table rows

I'm not a good graphics guy. In fact, I'd hire somebody to do web design if I have a web application project. For me, anything to do with aesthetics is totally out of the picture. I wouldn't want to spend a couple of hours designing and mixing colors and pictures. In reality, anything that has something to do with web requires visual arts. Same thing with reports. You really have to find the right color combination and layout for your reports. I was struggling with how to do this on tables which display results of my dataset queries. All I know is that I can display my resultsets with a single color. I know I can do this in the dataGrid control in ASP.NET 1.1 but I am creating my reports in SQL Server 2005 Reporting Services. After searching thru my favourite (and everybody's probably) search engine, I found the answer. You can use expressions in just about any property in your reports. To do alternating background colors in a table, you can simply use an expression like this: =IFF(RowNumber(Nothing) Mod 2,"color1","color2") where color1 and color2 are the names which you can get from the list of colors I don't guarantee that it will look nice - that depends on your visual aesthetics and how you appreciate color combination. What I did in my case was to extend this expression since I had a different color for my column header to identify the column names. My new expression would be =IIF((RowNumber(Nothing)+1) Mod 2,"color1","color2") so that the new color would start on the second row after the column header instead of the first one. I might be needing this in other functionalities like generating a report using the Matrix control so I better start looking for alternatives as early as now

Embed SQL Server 2005 Reporting Services Reports in your .NET applications using the ReportViewer control in Visual Studio 2005

Picture this - you work with a team, you're in charge of creating the front-end for your .NET applications and you need to have access to SQL Server 2005 Reporting Services reports that have already been developed and currently being used. What would you do? You don't want to recreate those reports, do you? Neither would you want to simlpy create a link from your application to those reports as this might look unprofessional. Well, Microsoft just has the solution to your need. The Microsoft Report Viewer Redistributable 2005 is a freely redistributable control that enables you to embed SQL Server 2005 Reporting Services reports in your .NET 2.0 applications. Whether you are creating ASP.NET or Windows Forms, it's easy to simply drag and drop a control that lets you access an already existing report. This gives you the full capabilities of SQL Server 2005 Reporting Services (well, you are calling it using the tool in the first place) like exporting to PDF, Excel, pictures, etc. You will see this being used by other Microsoft applications like Windows Server Update Services (WSUS) v3.0 where it requires you to have the Report Viewer 2005 Redistributable during deployment. Check out this site for more information on the ReportViewer control

Lost your Show Desktop icon? Try re-creating it back

I always use my Show Dektop icon especially when I've got tons of application window opened. But I never had to recreate the icon until now. A friend of mine asked me how to re-create the Show Desktop icon as he accidentally deleted his permanently. My first instinct was to simply look at the properties of the shortcut on the Quick Launch toolbar. But that didn't give me enough information to recreate the shortcut. All I found out is that it's just a variation of the Explorer.exe command. To re-create your Show Desktop icon, open your favourite text editor (Notepad, TextPad, etc.) Then, type the following text

[Shell]
Command=2
IconFile=explorer.exe,3
[Taskbar]
Command=ToggleDesktop

Save the file with a filename of Show Desktop.scf (now you can name it anything you want like I Wanna See My Desktop as long as the extension is scf). This Microsoft KB article explains in detail how to do it.

Importing Word and Excel as InfoPath 2007 Forms

If you have been working with business forms, you probably have created a few in either Microsoft Word or Excel. One thing to make managing business forms a lot easy is to upload them in your Sharepoint portal. This makes it easy to have a standardized form easily available to the rest of the organization. You can also port them over as InfoPath forms. In Sharepoint 2007, you have the option to use Forms Services instead of individual forms. This makes it easy for users to have a unified and standardized format plus the advantage of binding the form to a backend database for data collection. If you have existing Word or Excel forms, you can easily import and convert them in InfoPath. Just run the Import Form Wizard in InfoPath to convert your existing Word and Excel forms to InfoPath forms - no need to re-create those forms. For more information, check out the Introduction to importing and exporting form data and form templates

When SQL Server backups can't be restored

We always think that having a backup is more than enough to keep us afloat during a disaster. I always say that any backup will be useless unless it is tested. I came across one case where a SQL Server 2000 backup for the master database cannot be restored because it was generated using a different patch set for SQL Server 2000. The backups were generated before applying SQL Server 2000 hotfix 2191. After the patches were applied, a restore was attempted but failed. An error was encountered which specifies that the backup was generated by an earlier version of SQL Server. A best practice approach is to generate backups before and after a patch will be applied. This makes sure that you have valid backups in case something happened. It may be a bit costly as far as disk space is concerned but it sure beats not having a valid backup. It may even save you from spending sleepless nights trying to rollback to the patchset which generated the backups.