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!