Sending SQL Notifications with CDOSYS

Question

Sending SQL Notifications with CDOSYS

Desirée B. Harris

SSC Veteran

Points: 251
More actions
September 9, 2004 at 12:00 am

#161418

Comments posted to this topic are about the item Sending SQL Notifications with CDOSYS

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Clayton Groom SSC-Addicted Points: 410 More actions · Answer 1

A friend of mine took the Microsoft example proc and produced a more robust version. It defaults to using the servername from @@servername if one is not provided, and it can send file attachments:

use

master

go

CREATE

PROCEDURE [dbo].[sp_sendmail_CDOSYS]

@To

varchar(100) ,

@Subject

varchar(100)=" ",

@Body

varchar(4000) =" ",

@Importance

int = 1, -- 0=low, 1=normal, 2=high

@cc

varchar(100)= " " ,

@Bcc

varchar(100)= " " ,

@Attachments

varchar(8000)=NULL, /* seperated by ; */

@HTMLFormat

int = 0,

@From

varchar(100)= null ,

@server

varchar(255) = null,

@UserName

varchar(255) = null,

@password

varchar(255) = null

/*********************************************************************

Created by : Corey Embry

Description: Use CDOSYS to Send Mail using smtp

Base Code was obtained from MS Article Q312839

This stored procedure takes the parameters and sends an e-mail.

All the mail configurations are hard-coded in the stored procedure.

Comments are added to the stored procedure where necessary.

References to the CDOSYS objects are at the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

exec sp_sendmail_CDOSYS

@To = 'Coreye@avalamarketing.com'

, @Subject = 'CDOSYStest' -- Optional

, @Body ='this is a test' -- Optional

, @Importance int = 1 -- Optional

, @cc = 'mickey@disney.com' -- Optional

, @Bcc = 'Minnie@disney.com' -- Optional

, @Attachments = 'c:\test.txt' -- Optional ; Delimited

, @HTMLFormat = 0 -- Optional Default Text

-- , @From = 'Coreye@avalamarketing.com' -- Optional

, @server='avalafs1' -- Optional

-- , @UserName= -- Optional

-- , @Password= -- Optional

***********************************************************************/

AS

SELECT @server = isnull(@server, @@SERVERNAME)

Select @From = isnull(@From, @@SERVERNAME)

Declare @iMsg int

Declare @hr int

Declare @int int

Declare @Tmp varchar(8000)

Declare @source varchar(255)

Declare @description varchar(500)

Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************

-- This is to configure a remote SMTP server.

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server

if @UserName is not null and @password is not null

begin

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value', @UserName

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value', @Password

end

-- Save the configurations to the message object.

-- EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc

EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC

/* if there are any attachments */

if

len(@Attachments)>0

begin

while len(@Attachments)>0

begin

set @int = charindex(';',@Attachments)

if @int > 0

begin

set @Tmp = left(@Attachments,@int-1)

set @Attachments = right(@Attachments,(len(@Attachments)-@int))

end else

begin

set @Tmp = @Attachments

set @Attachments = ''

end /* Add the Attachment */

Print @Tmp

EXEC @hr= sp_OAMethod @iMsg, 'AddAttachment', NULL, @Tmp

end

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

if

@HTMLFormat <> 1

begin

EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

end

else

begin

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

end

-- Save the configurations to the message object.

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0

select @hr

BEGIN

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

-- Do some error handling after each step if you have to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

GO

Kindest Regards,

Clayton

kenwallacedesign Old Hand Points: 385 More actions · Answer 2

This technique is an oldie but a goodie.

One of Clinton Herring's contributions to "Script of the Day" a while back (I can't remember the date, but the script is dated 08/22/2001) does this too, and, might I add, is a bit more thorough than both examples posted above (Perhaps it gets overlooked because the script title is potentially misleading).

In any event, Clinton's version of this idea uses an OLE automation implementation of the CDOSYS dll, includes error handling, a help/usage message, a clever technique to verify attachments, and, IMHO, is very well-factored.

In addition to any attachments you specify, Clinton's script can also perform a user-defined SQL Query (passed as a parameter to the SPROC), pipe the query results out to a text file (to the drive wih the most free space!), and attach the report to the email! A great idea for automating server reports and emailing them to the DBA, or, generating application-specific reports and emailing them to your business stakeholders.

See what you think!

SQL 2K SMTP mail on Windows 2K
http://www.sqlservercentral.com/scripts/contributions/510.asp
Script Rating Total number of votes [86]
By: Clinton Herring
SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example of an OLE automation implementation. This update corrects a problem when the proc is called twice in the same batch without an intervening 'Go'. The cause is the sp_OAStop. It needs to be removed or commented out. The stated method of operation in the BOL is incorrect. 11/5/2002 Some people have reported errors when running this stored procedure. They have not been failures of the stored procedure. They are errors related to improper configuration/permissions for the SQL server to use the local network SMTP relay server for either internal or out going mail. 11/20/2002 Fixes a problem related to the OSQL call to send an attached query. OSQL was not releasing its lock on the first output file it created until the session ended, hence, calling the proc in a cursor or loop prevented subsequent query attachments. 04/09/2003 Comment correction.

Best,

Ken

itboy Valued Member Points: 66 More actions · Answer 3

Works great, just do not forget to remove the space in this line!

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas. microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'

kmarshall Ten Centuries Points: 1001 More actions · Answer 4

I recently saw your article at http://www.orcsweb.com/articles/sendmailsql.aspx and it solved my problem perfectly.

Just wanted to say thanks.

Windows 2008 Server | SQL Server 2008

JBZ SSC-Addicted Points: 400 More actions · Answer 5

I am using very simple VBscript to achieve the same thing, and attachements are allowed as well after small modification

--------------------------------------

Set oMsg = CreateObject("CDONTS.NewMail")

oMsg.From = "abc"

oMsg.To = "abc"

oMsg.Subject = "you message"

oMsg.Body = "you message"

oMsg.Send

Set oMsg = Nothing

-------------------------------------

Julia

andyobl Right there with Babe Points: 767 More actions · Answer 6

Hi Clayton,

I can't see how the Importance flag will work. You didn't call the method anywhere in the code?

Regads,

Andy

B Thomas SSC Journeyman Points: 91 More actions · Answer 7

I like the suggestions, and the ability to attach a SQL query output to an email.

I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @sqlquery + '" /E''')

But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

I also do NOT want to use XP_SENDMAIL as it uses MAPI .

Is there any other way to attach SQL queries to an email ??

Tom Blaylock Say Hey Kid Points: 703 More actions · Answer 8

Has anyone been able to make the "Importance" or "Priority" work using CDOSYS ?

Spencer-206914 SSC Veteran Points: 224 More actions · Answer 9

I was using the sqlsmtpmail code and attaching a step to all of my jobs to exec the proc on failures. Problem is, how can I put the Job Name and/or step name that failed in the subject and/or body of the emails? I have everything else working.

Thanks all!

Spencer-206914 SSC Veteran Points: 224 More actions · Answer 10

Spencer-206914

SSC Veteran

Points: 224

May 11, 2005 at 9:11 am

#558123

Anyone?

Spencer-206914 SSC Veteran Points: 224 More actions · Answer 11

Spencer-206914

SSC Veteran

Points: 224

June 1, 2005 at 4:21 pm

#562382

double bump

Don Staten SSC Eights! Points: 957 More actions · Answer 12

I have also been unable to find an answer to this question. Can anyone answer this?

dseph2000 Valued Member Points: 61 More actions · Answer 13

I would like to reccomend to not send email by port using CDOSYS in a stored proceedure. There are problems with doing this which may not manifest until right away. If you do so, you will probably run into an issue when setting one of the items in the configuration's fields collection (ie an access violation). Its best to send via pickup directory when calling CDOSYS via a stored proceedure. Another alternative is to put the codd into a vb script and to call via DTS. Over all sending using a pickup directory of a locally installed smtp server is better than sending via port: the proc code won't have to connect to a remote server, it will be more reliable (still works and is relaible if the networks or remote server has issues or is down) and will be much faster since the code can fire-and-forget (CDOSYS will just write the message file out to the local directory and your code is done). The SMTP server will handle things from there. I know many people don't want to install SMTP on a SQL server, however it does not take up many resources/processing time, its a system component and you get a much better and reliable configuration.

A quick thing to remember for CDOSYS: 1 if by land (pickup) 2 if by sea (port)

You may want to read my blog posting on issues with using CDOSYS under a stored proceedure.

http://blogs.msdn.com/webdav_101/archive/2008/02/01/why-is-cdosys-failing-in-a-stored-proceedure.aspx

I'll probably add more to the above blog entry later....

Thanks,

Dan

Emito Mr or Mrs. 500 Points: 556 More actions · Answer 14

Emito

Mr or Mrs. 500

Points: 556

January 15, 2009 at 10:44 am

#927544

Thanks You!

Works very well.

Regards, Emito.:D