CDONTS in sproc - query in message body

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CDONTS in sproc - query in message body

Author

Topic

nukeawhale
Starting Member

14 Posts

Posted - 2003-11-13 : 14:12:45

I am trying to use CDONTS to send mail using a stored procedure. However, the body of the message needs to be the result of query from a SQL table.

I can successfully send mail using CDONTS with the sproc, but I can not get the results of the query to be in the body of the message .

I am using the following sproc to send mail:

CREATE PROCEDURE [dbo].[stp_NewMail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Imp int,
@BodyFormat int,
@MailFormat int
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OASetProperty @MailID, 'Subject',@Subject
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Bcc', @BCC
EXEC @hr = sp_OASetProperty @MailID, 'Importance', @Imp --See Notes Below
EXEC @hr = sp_OASetProperty @MailID, 'BodyFormat', @BodyFormat --See Notes Below
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', @MailFormat --See Notes Below
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

To send the mail I use the following command:

EXEC stp_NewMail
/*FROM*/ 'you@you.com',
/* TO */ 'me@me.com',
/* SUBJECT */ 'New Mail',
/* BODY */ 'My message',
/* CC */ '',
/* BCC */ '',
/* Importance */ 2,
/* BODYfmt*/ 0,
/* Mailfmt*/ 0

How do I get the results of a query in the message of the body?
Just a simple query like (SELECT TOP 5 * FROM tblMytbl)?
I tried to create a variable to capture the results, but it's not working.

I was using this with xp_sendmail and the @query variable, but am having access errors and other errors now which is causing it to fail. I would rather convert to CDONTS if it is possible instead of using xp_sendmail.

Any help would be greatly appreciated.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-11-14 : 12:08:29

AFAIK, it is not possible to include the results of a query in a CDONTS (or CDOSYS) email sent from within a sproc without writing your own code to loop through the recordset and build the text of your email. CDONTS email does not have any automatic "append query results" command.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources