Using the FOR command to copy files listed in a text file

A coworker asked me for a script. Here’s the request:

… would want to copy all files on this list [an attached text document] to another location (doesn’t really matter where for now). All are currently located in \\server\share\folder. The path in the new location should begin with the part after “folder”…

In a nutshell, here is the high-level description of what the script must do:

Given a text file which provides a list of files, copy the files from a fixed source to a fixed destination, recreating the directory trees on the destination. A simple file copy won’t work because there may be files in the source folders which should not be copied.

Sample content of the text file is:

client\CD120\Samarai Legends\Drafts\drafts folder.txt
client\CD120\Samarai Legends\Inbox\Legends.doc
client\CD120\Bushido Warriors\Inbox\Warrior Code.doc

The solution to this is to make a simple batch file that parses the content of the text file, generating the appropriate xcopy command to copy the file. We’ll call the batch file xcopylist.bat; its one line of content is below. Change c:\temp\ to whatever path you want the files copied to. (I used c:\temp\ for testing.) Change \\server\share\folder to the root folder of the files to copy.

for /f "delims=" %%i in (filelist.txt) do echo D|xcopy "\\server\share\folder\%%i" "c:\temp\%%i" /i /z /y

Put the file list in the same directory as the batch file and name the file list filelist.txt. Then run the batch file and viola! You’ve got to love the for command, which lets you (among other things) parse text files and use the line-by-line output. Another trick in here is the output parser pipe, which allows us to automatically press the “D” key with each xcopy command.

Note: The batch file overwrites files in the destination automatically. To turn this off (have it prompt you), change /y to /y- in the batch file. However, if you’re using Windows NT 4.0, just delete the /y switch altogether – it’s only supported in Windows XP and Windows 2000.

How does ProxyBuster.net work?

I received an email today from Johnny B. (Good?), in which I was asked a question about a Web service I provide, ProxyBuster.net. The email was:

Hello. I had a little question and I’ll be so glad if you can answer me. I wanted to ask: What system or platform does ProxyBuster use in its process, is it CGI, PHP, ASP, JavaScript, or something else? I mean which one of these can manage to trick the firewall and access a forbidden file?

My response is: None of those, actually. The “trick” is understanding how firewalls work. Most firewalls/proxies do one of a few things:

  • Block by IP address.
  • Block by domain name.
  • Block by URL text.
  • Block by file extension.
  • Block by HTTP content type.
  • Block by actual content type.

Let’s say you want to access Google’s home page (http://www.google.com/index.html). A firewall can:

  • Block the IP address 216.239.39.99.
  • Block the domain name, www.google.com.
  • Block a URL that has “www.google.com” in it (as in http://www.google.com).
  • Block all files ending in “.html”.
  • Block all files that have the HTTP header content-type “text/html”.
  • Block all files that are actually text files.

In this case the first three bullet items are effective, but the last three would limit your ability to use the Internet as a whole. Usually binary files (ZIP files, EXE files, etc) are restricted by the latter three types of blocks, and entire sites by the first three types of blocks.

So how does ProxyBuster do its magic?

First, if your firewall/proxy blocks the IP address, that’s not a problem to ProxyBuster, because you do not connect to the site directly. ProxyBuster connects to the site and reads your data, then provides it to you. Of course, if your firewall/proxy blocked www.proxybuster.net or our IP address, you’d be out of luck using our service because you wouldn’t be able to connect to it!

Next, ProxyBuster returns the file to you in one of a few formats. First, the file is sent to you as “download.aspx” — which is a very generic file name and file extension that would be too restrictive to block. (For example, blocking “.aspx” files would make it impossible to browse Microsoft’s Web site.) Next, we give you the option to receive the file with an HTTP content-Type header “text/plain” even if the file is not a plain text file. Last, if your firewall/proxy blocks binary files, we can send you the file in a text-encoded format (so it’s transmitted as a text file); all you need to do is decode it after you receive it (which does require a separate utility).

I hope this sheds some light to the inner workings of ProxyBuster!

Moving a FrontPage Web site to a non-FrontPage Web site

For a web application I am working on, there are two Web sites – a development site and a production site. Both reside on the same server. Development is done using Microsoft Visual InterDev, so to connect to the development site, I have FrontPage extensions installed. (I also integrate with Microsoft Visual SourceSafe, but that’s meaningless to this discussion.)

Moving files from the development environment to the production environment usually entails manual copying, because I do not have FrontPage extensions installed on the production site (for obvious reasons; I don’t really need them!). I finally found a way to do it – the “old-fashioned” way – using a simple command: XCOPY.

XCOPY, which has been a part of Microsoft’s command shell (also known as DOS prompt) for years, copies files from a source to a destination with a wealth of options – most important for me, the ability to copy changed files only and to exclude files and folders.

First, you need an exclusions file – essentially a list of file name patterns (not wildcards). If a file or folder matches any pattern in the exclude file, it is skipped. Here’s my exclude file – which I named exclude.txt:

\temp\
\aspnet_client\
.vss
_vti
.scc

I’m telling XCOPY to skip the \temp\ and \aspnet_client\ folders, and skip any files that have .vss, .scc, or _vti in the filename. This ensures that unnecessary files are not copied to the production Web site.

The XCOPY command to run, which should work in Windows 2000 or Windows XP (not sure if Windows NT 4.0 supports all the XCOPY extensions here) is:

xcopy e:\devsite\*.* e:\website\ /D /P /E /V /R /-Y /EXCLUDE:exclude.txt

For you to use this, just change the source and destination paths, and viola! Dump this line into a batch file (call it dev2prod.bat) and make sure the exclude.txt file is in the same folder as the batch file.

Windows Server/SQL Server Performance Monitor Template

One of the nice things about Performance Monitor in Windows 2000/2003 is that it can be plugged into an HTML page as an ActiveX control. Even nicer than that is the fact that you can easily edit the parameters for the console by editing the HTML page.

Where I work, we have a handful of SQL servers. To monitor them, I created a performance monitor view with a handful of core counters on one server and saved it as an HTML file. (This is done using the Performance Monitor’s System Monitor. Simply set up your counters then right-click the monitor window and choose Save As.) Making subsequent views for other servers was as simple as making a copy of the HTML file, opening it in Notepad, and changing all server name instances.

Below is the contents of a simple performance monitor HTML page that can be used to monitor a server running Microsoft SQL Server 7.0 or higher. To use it, copy the contents into a text file, rename all instances of SERVERNAME to the name of your server, save it as a .htm file, and open it in Internet Explorer. Viola! Quick and easy view to your server’s core performance counters.

< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><html>
<head>
<meta NAME="GENERATOR" Content="Microsoft System Monitor">
</meta></head><body>
<object ID="DISystemMonitor1" WIDTH="100%" HEIGHT="100%"
CLASSID="CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347">
 <param NAME="_Version" VALUE="393219">
 </param><param NAME="_ExtentX" VALUE="28549">
 </param><param NAME="_ExtentY" VALUE="23839">
 </param><param NAME="DisplayType" VALUE="1">
 </param><param NAME="ReportValueType" VALUE="0">
 </param><param NAME="MaximumScale" VALUE="100">
 </param><param NAME="MinimumScale" VALUE="0">
 </param><param NAME="ShowLegend" VALUE="-1">
 </param><param NAME="ShowToolbar" VALUE="-1">
 </param><param NAME="ShowScaleLabels" VALUE="-1">
 </param><param NAME="ShowHorizontalGrid" VALUE="0">
 </param><param NAME="ShowVerticalGrid" VALUE="0">
 </param><param NAME="ShowValueBar" VALUE="-1">
 </param><param NAME="ManualUpdate" VALUE="0">
 </param><param NAME="Highlight" VALUE="0">
 </param><param NAME="ReadOnly" VALUE="0">
 </param><param NAME="MonitorDuplicateInstances" VALUE="-1">
 </param><param NAME="UpdateInterval" VALUE="5">
 </param><param NAME="DisplayFilter" VALUE="1">
 </param><param NAME="BackColorCtl" VALUE="-2147483633">
 </param><param NAME="ForeColor" VALUE="-1">
 </param><param NAME="BackColor" VALUE="-2147483633">
 </param><param NAME="GridColor" VALUE="8421504">
 </param><param NAME="TimeBarColor" VALUE="255">
 </param><param NAME="Appearance" VALUE="-1">
 </param><param NAME="BorderStyle" VALUE="0">
 </param><param NAME="NextCounterColor" VALUE="7">
 </param><param NAME="NextCounterWidth" VALUE="0">
 </param><param NAME="NextCounterLineStyle" VALUE="0">
 </param><param NAME="GraphTitle" VALUE="">
 </param><param NAME="YAxisLabel" VALUE="">
 </param><param NAME="DataSourceType" VALUE="1">
 </param><param NAME="SqlDsnName" VALUE="">
 </param><param NAME="SqlLogSetName" VALUE="">
 </param><param NAME="LogFileCount" VALUE="0">
 </param><param NAME="AmbientFont" VALUE="-1">
 </param><param NAME="LegendColumnWidths" VALUE="0.042654028436019 0.042654028436019 0.218957345971564 5.78199052132701E-02 4.92890995260664E-02 0.17914691943128 7.20379146919431E-02">
 </param><param NAME="LegendSortDirection" VALUE="0">
 </param><param NAME="LegendSortColumn" VALUE="2097272">
 </param><param NAME="CounterCount" VALUE="6">
 </param><param NAME="MaximumSamples" VALUE="100">
 </param><param NAME="SampleCount" VALUE="100">
 </param><param NAME="SampleIndex" VALUE="5">
 </param><param NAME="StepNumber" VALUE="8">
 </param><param NAME="Counter00001.Path" VALUE="\\SERVERNAME\Memory\Pages/sec">
 </param><param NAME="Counter00001.Color" VALUE="4210752">
 </param><param NAME="Counter00001.Width" VALUE="1">
 </param><param NAME="Counter00001.LineStyle" VALUE="0">
 </param><param NAME="Counter00001.ScaleFactor" VALUE="0">
 </param><param NAME="Counter00001.StatisticStatus" VALUE="0">
 </param><param NAME="Counter00002.Path" VALUE="\\SERVERNAME\Processor(_Total)\% Processor Time">
 </param><param NAME="Counter00002.Color" VALUE="128">
 </param><param NAME="Counter00002.Width" VALUE="1">
 </param><param NAME="Counter00002.LineStyle" VALUE="0">
 </param><param NAME="Counter00002.ScaleFactor" VALUE="0">
 </param><param NAME="Counter00002.StatisticStatus" VALUE="0">
 </param><param NAME="Counter00003.Path" VALUE="\\SERVERNAME\SQLServer:Buffer Manager\Buffer cache hit ratio">
 </param><param NAME="Counter00003.Color" VALUE="65535">
 </param><param NAME="Counter00003.Width" VALUE="1">
 </param><param NAME="Counter00003.LineStyle" VALUE="0">
 </param><param NAME="Counter00003.ScaleFactor" VALUE="0">
 </param><param NAME="Counter00003.StatisticStatus" VALUE="0">
 </param><param NAME="Counter00004.Path" VALUE="\\SERVERNAME\SQLServer:General Statistics\User Connections">
 </param><param NAME="Counter00004.Color" VALUE="16711680">
 </param><param NAME="Counter00004.Width" VALUE="1">
 </param><param NAME="Counter00004.LineStyle" VALUE="0">
 </param><param NAME="Counter00004.ScaleFactor" VALUE="-1">
 </param><param NAME="Counter00004.StatisticStatus" VALUE="0">
 </param><param NAME="Counter00005.Path" VALUE="\\SERVERNAME\SQLServer:Locks(_Total)\Average Wait Time (ms)">
 </param><param NAME="Counter00005.Color" VALUE="16711935">
 </param><param NAME="Counter00005.Width" VALUE="1">
 </param><param NAME="Counter00005.LineStyle" VALUE="0">
 </param><param NAME="Counter00005.ScaleFactor" VALUE="0">
 </param><param NAME="Counter00005.StatisticStatus" VALUE="0">
 </param><param NAME="Counter00006.Path" VALUE="\\SERVERNAME\System\Processor Queue Length">
 </param><param NAME="Counter00006.Color" VALUE="16776960">
 </param><param NAME="Counter00006.Width" VALUE="1">
 </param><param NAME="Counter00006.LineStyle" VALUE="0">
 </param><param NAME="Counter00006.ScaleFactor" VALUE="1">
 </param><param NAME="Counter00006.StatisticStatus" VALUE="0">
 </param><param NAME="Selected" VALUE="\\SERVERNAME\Memory\Pages/sec">
</param></object>
</body>
</html>

Exporting directories to delimited text files

A coworker of mine needed some way to get a list of all files in a directory (and its subdirectories) in a delimited text file. Specifically, they needed a pipe-delimited text file with the following fields:

  • folder path (the absolute path to the folder containing the file)
  • modified date
  • modified time
  • size, in bytes
  • file name

The solution was a VBScript using Windows Scripting technologies. It’s rather simple, utilizing the Scripting.FileSystemObject object. We retrieve the current directory using the GetFolder() function, which returns a Folders collection (i.e., a collection of Folder objects). This object is passed as a parameter to the ParseFolder() function in our script.

The ParseFolder() function gets the data from each File object in its Files collection and outputs it to a pipe-delimited text file (called diraudit.txt. It then calls itself (a recursive function) for each Folder object in its Folders collection. The recursion takes care of all the subsequent subfolders.

The actual code of the script, which I named diraudit.vbs, follows.

Option Explicit

Const ForWriting = 2

Sub ParseFolder ( objFolder )

	Dim objFiles, f
	Set objFiles = objFolder.Files
	
	For Each f In objFiles
		objOutputFile.WriteLine Mid( f.Path, 1, InStr( f.Path, f.Name ) - 1 ) & "|" & _
			FormatDateTime( f.DateLastModified, vbShortDate ) & "|" & _
			FormatDateTime( f.DateLastModified, vbShortTime ) & "|" & _
			f.Size & "|" & f.Name
	Next

	Set objFiles = Nothing

	Dim objFolders
	Set objFolders = objFolder.SubFolders

	For Each f In objFolders
		ParseFolder( f )
	Next

	Set objFolders = Nothing

End Sub

Dim objFSO, objFolder, objOutputFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder( "." )

Set objOutputFile = objFSO.OpenTextFile("diraudit.txt", ForWriting, True)
objOutputFile.WriteLine "path|date|time|size|name"

Call ParseFolder( objFolder )

objOutputFile.Close

Set objOutputFile = Nothing
Set objFolder = Nothing
Set objFSO = Nothing

Running a Windows NT Domain / Active Directory Audit

Getting an audit of your IT environment is incredibly useful. When you consider an audit from a security perspective – in the Windows NT domain or Active Directory model – there are a few items of significant importance: domains, users, groups, computers, and services.

The first step in conducting an audit is discovery – find out what’s out there. Fortunately, we have a great scripting tool for this: ADSI (Active Directory Services Interface). ADSI can pull the information for us, but to utilize it, we need to store it somewhere – such as in a SQL database.

To save you the trouble of figuring this out, below is a (rather long) script which uses ADSI to poll your network and store data into text files. To run it successfully, you must have at least query access to your entire directory. (The script has been tested on an NT 4.0 domain and on an Active Directory site.) The script queries each domain, obtaining all users, groups (including group memberships), and computers; and for each computer, obtains all local users, groups (and group memberships), and services. The output is saved into pipe-delimited text files for easy import into a database.

The script, which I save as netquery.vbs, has the following syntax:

cscript.exe netquery.vbs [nodomainusers] [nodomaingroups] [nocomputers] [nolocalusers] [nolocalgroups] [noservices]

nodomainusers    Suppresses querying of domain users and groups
nodomaingroups   Suppresses querying of domain groups
nocomputers      Suppresses querying of computers, local users and groups, and services
nolocalusers     Suppresses querying of local users and groups
nolocalgroups    Suppresses querying of local groups
noservices       Suppresses querying of services

The script generates text files in the current directory. Note that existing files will be overwritten! Each text file has a header as its first row. Import each text file into a database table with the exact same name. References between tables is logical if you look at the column names. Each object has an ADSPath, which is unique. Objects may reference an ADSOwner, which is the object’s parent (such as the domain or computer for which a user or group belongs, or the computer which a service is installed on). One special file exists – the groupmembers file. This has only two fields – ADSOwner and ADSMember – which are used to correlate the group (ADSOwner) with its many members (ADSMember).

Play around with it and it’ll make sense. If your network is huge, this will take a long time to run! You can tell things are working if data is being added to the output files.

And now, with no further delays, is the master script: netquery.vbs!

Option Explicit

On Error Resume Next

Const ForWriting = 2

'ARGUMENTS
Dim blnDomainUsers, blnDomainGroups, blnComputers, blnLocalUsers, blnLocalGroups, blnServices

blnDomainUsers = True
blnDomainGroups = True
blnComputers = True
blnLocalUsers = True
blnLocalGroups = True
blnServices = True
For Each item In WScript.Arguments
	Select Case LCase(item)
		Case "nodomainusers" 
			blnDomainUsers = False
			blnDomainGroups = False
		Case "nodomaingroups"
			blnDomainGroups = False
		Case "nocomputers"
			blnComputers = False
			blnLocalUsers = False
			blnLocalGroups = False
			blnServices = False
		Case "nolocalusers"
			blnLocalUsers = False
			blnLocalGroups = False
		Case "nolocalgroups"
			blnLocalGroups = False
		Case "noservices"
			blnServices = False
	End Select
Next

'CREATE FILESYSTEMOBJECT
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objDomainsFile, objUsersFile, objGroupsFile, objGroupMembersFile, objComputersFile, objServicesFile
Set objDomainsFile = objFSO.OpenTextFile("domains.txt", ForWriting, True)
Set objUsersFile = objFSO.OpenTextFile("users.txt", ForWriting, True)
Set objGroupsFile = objFSO.OpenTextFile("groups.txt", ForWriting, True)
Set objGroupMembersFile = objFSO.OpenTextFile("groupmembers.txt", ForWriting, True)
Set objComputersFile = objFSO.OpenTextFile("computers.txt", ForWriting, True)
Set objServicesFile = objFSO.OpenTextFile("services.txt", ForWriting, True)

objDomainsFile.WriteLine "adspath|name"
objUsersFile.WriteLine "adspath|adsowner|name|fullname|description|passwordage|lastlogin|lastlogoff|raspermissions|accountdisabled"
objGroupsFile.WriteLine "adspath|adsowner|name"
objGroupMembersFile.WriteLine "adsowner|adsmember"
objComputersFile.WriteLine "adspath|adsowner|name|passwordage|owner|division|operatingsystem|osversion|processor|processorcount"
objServicesFile.WriteLine "adspath|adsowner|name|displayname|account"

Dim objDomains, objDomain, objUsers, objUser, objGroups, objGroup
Dim objComputers, objComputer, objComputerAccount, objServices, objService
Dim s1, s2, s3, s4, s5, s6, s7, obj, cls, op, item

Set objDomains = GetObject("WinNT:")

For Each objDomain in objDomains
	objDomainsFile.Write objDomain.AdsPath
	objDomainsFile.Write "|"
	objDomainsFile.Write objDomain.Name
	objDomainsFile.WriteLine
	
	'DOMAIN USERS
	If blnDomainUsers Then
		Set objUsers = Nothing
		Set objUser = Nothing
		Set objUsers = GetObject(objDomain.AdsPath)
		objUsers.Filter = Array("User")

		For Each objUser in objUsers
			objUsersFile.Write objUser.AdsPath
			objUsersFile.Write "|"
			objUsersFile.Write objDomain.AdsPath
			objUsersFile.Write "|"
			objUsersFile.Write objUser.Name
			objUsersFile.Write "|"
			objUsersFile.Write objUser.FullName
			objUsersFile.Write "|"
			objUsersFile.Write objUser.Description
			objUsersFile.Write "|"
			objUsersFile.Write objUser.PasswordAge
			objUsersFile.Write "|"
			objUsersFile.Write objUser.LastLogin
			objUsersFile.Write "|"
			objUsersFile.Write objUser.LastLogoff
			objUsersFile.Write "|"
			objUsersFile.Write objUser.RasPermissions
			objUsersFile.Write "|"
			If objUser.UserFlags And &H0002 Then 
				objUsersFile.Write "1"
			Else 
				objUsersFile.Write "0"
			End If
			objUsersFile.WriteLine
		Next
	End If
	
	'DOMAIN GROUPS
	If blnDomainGroups Then
		Set objGroups = Nothing
		Set objGroup = Nothing
		Set objGroups = GetObject(objDomain.AdsPath)
		objGroups.Filter = Array("Group")

		For Each objGroup in objGroups
			objGroupsFile.Write objGroup.AdsPath 
			objGroupsFile.Write "|"
			objGroupsFile.Write objDomain.AdsPath
			objGroupsFile.Write "|"
			objGroupsFile.Write objGroup.Name
			objGroupsFile.WriteLine

			For Each objUser In objGroup.Members
				If Right(objUser.Name, 1) <> "$" Then
					objGroupMembersFile.Write objGroup.AdsPath
					objGroupMembersFile.Write "|"
					objGroupMembersFile.Write objUser.AdsPath
					objGroupMembersFile.WriteLine
				End If
			Next
		Next
	End If
	
	'COMPUTERS
	If blnComputers Then
		Set objComputers = Nothing
		Set objComputer = Nothing
		Set objComputers = GetObject(objDomain.AdsPath)
		objComputers.Filter = Array("Computer")

		For Each objComputer in objComputers
			Set objComputerAccount = GetObject(objComputer.AdsPath & "$,user")

			objComputersFile.Write objComputer.AdsPath
			objComputersFile.Write "|"
			objComputersFile.Write objDomain.AdsPath
			objComputersFile.Write "|"
			objComputersFile.Write objComputer.Name 
			objComputersFile.Write "|" 
			objComputersFile.Write objComputerAccount.PasswordAge 
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.Owner
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.Division
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.OperatingSystem
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.OperatingSystemVersion
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.Processor
			objComputersFile.Write "|" 
			objComputersFile.Write objComputer.ProcessorCount
			objComputersFile.WriteLine

			'COMPUTER USERS
			If blnLocalUsers Then
				Set objUsers = Nothing
				Set objUser = Nothing
				Set objUsers = GetObject(objComputer.AdsPath & ",computer")
				objUsers.Filter = Array("User")

				For Each objUser in objUsers
					objUsersFile.Write objUser.AdsPath
					objUsersFile.Write "|"
					objUsersFile.Write objComputer.AdsPath
					objUsersFile.Write "|"
					objUsersFile.Write objUser.Name
					objUsersFile.Write "|"
					objUsersFile.Write objUser.FullName
					objUsersFile.Write "|"
					objUsersFile.Write objUser.Description
					objUsersFile.Write "|"
					objUsersFile.Write objUser.PasswordAge
					objUsersFile.Write "|"
					objUsersFile.Write objUser.LastLogin
					objUsersFile.Write "|"
					objUsersFile.Write objUser.LastLogoff
					objUsersFile.Write "|"
					objUsersFile.Write objUser.RasPermissions
					objUsersFile.Write "|"
					If objUser.UserFlags And &H0002 Then 
						objUsersFile.Write "1"
					Else 
						objUsersFile.Write "0"
					End If
					objUsersFile.WriteLine
				Next
			End If
			
			'COMPUTER GROUPS
			If blnLocalGroups Then
				Set objGroups = Nothing
				Set objGroup = Nothing
				Set objGroups = GetObject(objComputer.AdsPath & ",computer")
				objGroups.Filter = Array("Group")

				For Each objGroup in objGroups
					objGroupsFile.Write objGroup.AdsPath 
					objGroupsFile.Write "|"
					objGroupsFile.Write objComputer.AdsPath
					objGroupsFile.Write "|"
					objGroupsFile.Write objGroup.Name
					objGroupsFile.WriteLine

					For Each objUser In objGroup.Members
						If Right(objUser.Name, 1) <> "$" Then
							objGroupMembersFile.Write objGroup.AdsPath
							objGroupMembersFile.Write "|"
							objGroupMembersFile.Write objUser.AdsPath
							objGroupMembersFile.WriteLine
						End If
					Next
				Next
			End If

			'SERVICES
			If blnServices Then
				Set objServices = Nothing
				Set objService = Nothing
				Set objServices = GetObject(objComputer.AdsPath & ",computer")
				objServices.Filter = Array("Services")

				For Each objService In objServices
					objServicesFile.Write objService.AdsPath 
					objServicesFile.Write "|"
					objServicesFile.Write objComputer.AdsPath
					objServicesFile.Write "|"
					objServicesFile.Write objService.Name
					objServicesFile.Write "|"
					objServicesFile.Write objService.DisplayName
					objServicesFile.Write "|"
					objServicesFile.Write objService.ServiceAccountName
					objServicesFile.WriteLine
				Next
			End If
		Next
	End If
	
Next

objServicesFile.Close
objComputersFile.Close
objGroupMembersFile.Close
objGroupsFile.Close
objUsersFile.Close
objDomainsFile.Close
Set objServicesFile = Nothing
Set objComputersFile = Nothing
Set objGroupMembersFile = Nothing
Set objGroupsFile = Nothing
Set objUsersFile = Nothing
Set objDomainsFile = Nothing

ASP.Net Web Forms, BASE HREF, and FORM ACTIONs

In some of the sites I develop, I use the BASE HREF tag. Unfortunately, doing this poses problems when you’re using Web forms in ASP.Net if your Web form is in a directory below the root directory.

If I create a page using Web forms in a subdirectory – as in http://somehostname/subfolder/webform.aspx – the FORM ACTION property of the form will point to http://somehostname/webform.aspx. Note how subfolder disappeared. This happens because of the BASE HREF tag. ASP.Net Web forms create the FORM ACTION without any reference to the physical path of the file – something that is not a problem unless you’re using BASE HREF.

Unfortunately, in a server-side FORM tag, you can not specify the ACTION property. But you can use JavaScript to change it. So, on each Web page where I need to adjust the FORM ACTION of a server-side ASP.Net Web form, I do something like this:

I use JavaScript to compile the complete path to the form, which includes the base URL (which I store in Application["basehref"]) followed by the subfolder (which I must type explicitly) followed by the current form action (which is taken dynamically within JavaScript). In four simple lines, I work around an ASP.Net shortcoming and continue to allow myself the benefit of using the BASE HREF tag!

COM Objects in SQL: Sending E-mail using SQL and CDONTS

Microsoft’s SQL Server provides e-mail services via the SQLMail service. Unfortunately, this service has some significants limitations. It uses MAPI, requiring an Outlook mail profile (usually tied with a Microsoft Exchange Server mailbox). As a result, all messages created will come from the same mailbox (everything is “from” the same person). For those who host multiple Web sites on their server, this may not do – you’d want one site to send messages from admin@domaina.com, another to send messages from admin@domainb.com, and so forth.

There is a free solution to this problem: use CDONTS. CDONTS, short for Collaborative Data Objects for NT Server, allows you to use a COM object to send email through the Microsoft SMTP service running on the same computer.

This article will not go in-depth into CDONTS, but it will show you how to interface with COM objects via SQL, and you’ll end up with a handy stored procedure which allows you to send customizable e-mail without relying on SQLMail.

Requirements

The scripts in this article have been written on a computer with Windows 2000 Server, SQL Server 2000, and the SMTP service installed. (The code will work fine on a computer with Windows 2000 Professional, SQL Server 2000, and the SMTP service.) Although I haven’t tested it, the code should work on Windows NT 4.0 Server, SQL Server 7.0, and the SMTP service (part of the Windows NT 4.0 Option Pack). It will not work with Windows NT 4.0 Workstation, which does not have the capability to run the SMTP service.

Note for SQL 7 users: There is a limitation in SQL 7 that truncates all strings passed to COM objects to 255 characters. As a result, even though the datatypes in the stored procedure allow more than 255 characters, only the first 255 characters will be passed to the CDONTS COM object. (It’s a limitation of the sp_OA… methods.) The only known workaround is to upgrade to SQL 2000 (not an inexpensive workaround!). One reader, Daniel M., notes that he was able to work around this by changing the VARCHAR fields to TEXT fields (a theoretical upper limit was not tested, but this goes beyond the 255 character limit).

Step 1: How it works – COM objects in SQL

The core functionality we will use is provided by a handful of SQL system stored procedures, grouped as the OLE Automation Extended Stored Procedures in MSDN. We’re concerned with five of these.

sp_OACreate – Creates an instance of a COM object
sp_OADestroy – Destroys an instance of a COM object
sp_OAMethod – Calls the method of a COM object
sp_OASetProperty – Sets the property of a COM object
sp_OAGetProperty – Gets the property of a COM object

Each of these stored procedures returns an error code. Anything but a return value of zero indicates an error. (We’re not concerned with handling different types of errors here; we’ll assume every error is critical.)

Step 2: Creating and Destroying Object References

Before you can work with a COM object, you have to create an instance of it with the sp_OACreate stored procedure. When you’re done working with the instance you created, you should destroy it with the sp_OADestroy stored procedure. Destroying every instance of an object when you’re done with it is good programming practice – always remember to do it!

sp_OACreate takes two parameters. The first is a VARCHAR() (string) parameter that contains either the class identifier (ClsID) or the programmatic identifier (ProgID) of the COM object. For the CDONTS “NewMail” object which we’ll be using, the ProgID (which is much easier to remember and use than class IDs) is CDONTS.NewMail. The second parameter is an INT OUTPUT (integer output) parameter which returns the object token for the instance created. This is required when calling subsequent OLE stored procedures.

sp_OADestroy takes one parameter: the object token of the COM object that we received when we called sp_OACreate. (See why it’s important to keep that value?)

The following block of code illustrates how to create the instance of the COM object and destroy it. Note how we’re trapping errors using the return values of each stored procedure.

    DECLARE @result INT
    DECLARE @object INT

    PRINT 'Creating the CDONTS.NewMail object'
    EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

    IF @result <> 0
        PRINT 'sp_OACreate Failed'
    ELSE BEGIN
        PRINT 'Destroying the CDONTS.NewMail object'
        EXEC @result = sp_OADestroy @object
        IF @result <> 0
            PRINT 'sp_OADestroy Failed'
    END

Step 3: Setting and Getting Object Properties

Now that we have our object reference, we can set and get the properties of the object. Setting a property is done using the sp_OASetProperty stored procedure; getting a property is done using the sp_OAGetProperty stored procedure.

sp_OASetProperty takes three parameters. The first is the object token of the COM object that we received when we called sp_OACreate. The second parameter is a VARCHAR() (string) parameter that contains the property name you wish to set the value of. The third parameter is an OUTPUT parameter that contains the value of the property. Note that the datatype of this last parameter depends on the datatype of the property – strings, VARCHAR()s, integers to INTs, and so forth. If the property returns an object reference, use an INT datatype – this can then be used as an object token, much like we use the @object variable in our sample code.

sp_OAGetProperty also takes three parameters. The first and second parameters are the same as those of sp_OASetProperty – the object token of the COM object and the property name you wish to get the value of. The third parameter is the value you wish to set the property to. Again, the datatype of the last parameter depends on the datatype of the property – strings, integers, object tokens, and the like.

The following block of code adds these two stored procedures to our existing code, and illustrates how to get a property value (the “Version” property) and set a property value (the “From” property). Error trapping is getting a bit difficult to look at, but we’ll fix this when we transform this code into a stored procedure later.

    DECLARE @result INT
    DECLARE @object INT
    DECLARE @propertyvalue VARCHAR(255)

    PRINT 'Creating the CDONTS.NewMail object'
    EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

    IF @result <> 0
        PRINT 'sp_OACreate Failed'
    ELSE BEGIN
        PRINT 'Get the From property'
        EXEC @result = sp_OAGetProperty @object, 'Version', @propertyvalue OUTPUT
        IF @result <> 0
            PRINT 'sp_OAGetProperty Failed'
        ELSE BEGIN
            PRINT 'CDONTS Version = ' + @propertyvalue
            PRINT 'Set the From property'
            EXEC @result = sp_OASetProperty @object, 'From', 'brian@demarzo.net'
            IF @result <> 0
                PRINT 'sp_OASetProperty Failed'
            ELSE BEGIN
                PRINT 'Destroying the CDONTS.NewMail object'
                EXEC @result = sp_OADestroy @object
                IF @result <> 0
                    PRINT 'sp_OADestroy Failed'
            END
        END
    END

Step 4: Calling Object Methods

To make our object actually do something, we usually have to call one of its methods. You call a method using the sp_OAMethod stored procedure.

sp_OAMethod takes a variable number of parameters, depending on the number of parameters required by the method being called. The first parameter is the object token of the COM object. The second parameter is a VARCHAR() (string) parameter that contains the name of the method you wish to call. The third parameter is an OUTPUT parameter that contains the return value of the method. The datatype of this parameter depends on the datatype of the return value (just like the last parameter in the sp_OAGetProperty stored procedure). If you do not need the return value, or if there is none, simply specify NULL. The fourth and subsequent parameters are passed as parameters to the method – datatypes depending on what the method expects. If you expect the method to return a value via the parameter, you must use a local variable and the OUTPUT keyword to retrieve the value.

(This stored procedure is one of the most complex, and most powerful, of those discussed here, so don’t be alarmed if it’s a bit confusing. When you see it used, it’ll make much more sense.)

We’ve added a call to sp_OAMethod to our sample code (below) to call the “Send” method of our object. This method takes four parameters: “From”, “To” “Subject”, and “Body” – each corresponding to the appropriate part of the e-mail to send. It does not return a value, so we will use NULL for the third parameter of our stored procedure.

    DECLARE @result INT
    DECLARE @object INT
    DECLARE @propertyvalue VARCHAR(255)

    PRINT 'Creating the CDONTS.NewMail object'
    EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

    IF @result <> 0
        PRINT 'sp_OACreate Failed'
    ELSE BEGIN
        PRINT 'Get the From property'
        EXEC @result = sp_OAGetProperty @object, 'Version', @propertyvalue OUTPUT
        IF @result <> 0
            PRINT 'sp_OAGetProperty Failed'
        ELSE BEGIN
            PRINT 'CDONTS Version = ' + @propertyvalue
            PRINT 'Set the From property'
            EXEC @result = sp_OASetProperty @object, 'From', 'brian@demarzo.net'
            IF @result <> 0
                PRINT 'sp_OASetProperty Failed'
            ELSE BEGIN
                PRINT 'Sending the message using the Send method'
                EXEC @result = sp_OAMethod @object, 'Send', NULL, 'brian@demarzo.net', 'brian@demarzo.net', 
                    'My test message', 'Hello world! Look at my body!'
                IF @result <> 0
                    PRINT 'sp_OAMethod Failed'
                ELSE BEGIN
                    PRINT 'Destroying the CDONTS.NewMail object'
                    EXEC @result = sp_OADestroy @object
                    IF @result <> 0
                        PRINT 'sp_OADestroy Failed'
                END
            END
        END
    END

Step 5: Bringing it together – SP_CDONTS_NewMail_Send

Now that we know how everything works, we’ll create a stored procedure that does the work for us. The SP_CDONTS_NewMail_Send stored procedure will accept four parameters: “From”, “To” “Subject”, and “Body”. These will be passed to the “Send” method.

Since we’re working in the confines of a stored procedure, we can trap errors a bit more efficiently, returning the error code to the caller using the RETURN command.

The sample code below includes the script to create the stored procedure, then a bit of sample code to illustrate how to interface with it. Note how, in our stored procedure, we used VARCHAR(8000) as the datatype for each of our parameters. This may be overkill, but it illustrates what our capabilities truly are.

    --start stored procedure code
    CREATE PROCEDURE SP_CDONTS_NewMail_Send
        @from VARCHAR(8000),
        @to VARCHAR(8000),
        @subject VARCHAR(8000),
        @body VARCHAR(8000)

    AS

    DECLARE @result INT
    DECLARE @object INT

    PRINT 'Creating the CDONTS.NewMail object'
    EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT
    IF @result <> 0
    BEGIN
        PRINT 'sp_OACreate Failed'
        RETURN @result
    END

    PRINT 'Sending the message using the Send method'
    EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body
    IF @result <> 0
    BEGIN
        PRINT 'sp_OAMethod Failed'
        RETURN @result
    END

    PRINT 'Destroying the CDONTS.NewMail object'
    EXEC @result = sp_OADestroy @object
    IF @result <> 0
    BEGIN
        PRINT 'sp_OADestroy Failed'
        RETURN @result
    END

    RETURN 0

    GO
    --end stored procedure code

    DECLARE @result INT
    EXEC @result = SP_CDONTS_NewMail_Send 'brian@demarzo.net',
        'brian@demarzo.net',
        'My test message',
        'Hello world! Look at my body!'

    PRINT @result

Summary: COM, SQL, and SP_CDONTS_NewMail_Send

There’s much more functionality that CDONTS provides which isn’t touched on here, including support for attachments, blind carbon copy (bcc), and more – so be sure to check out Microsoft’s CDO for NTS site on MSDN, under Messaging and Collaboration.

As well, you can interface with any COM object via SQL – including other mail components, such as /n software’s IP*Works!; Windows Script components; and thousands of others. It’s a great way to expand the capabilities of SQL!