Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sending SQL Notifications with CDOSYS Expand / Collapse
Author
Message
Posted Wednesday, August 25, 2004 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:47 AM
Points: 19, Visits: 77
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist
Post #133926
Posted Friday, August 27, 2004 1:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 3, 2014 3:40 PM
Points: 67, Visits: 28

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
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
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 Groom
covenanttechnologypartners.com
Post #134154
Posted Thursday, September 9, 2004 4:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:14 PM
Points: 11, Visits: 59

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




Post #136016
Posted Thursday, September 9, 2004 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 1, 2010 7:33 AM
Points: 6, Visits: 24
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'




Post #136046
Posted Thursday, September 9, 2004 8:40 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:42 AM
Points: 97, Visits: 83

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
Post #136098
Posted Thursday, September 9, 2004 2:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 1:11 PM
Points: 74, Visits: 168

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

Post #136173
Posted Thursday, October 21, 2004 9:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 2:15 AM
Points: 21, Visits: 101
Hi Clayton,
I can't see how the Importance flag will work. You didn't call the method anywhere in the code?

Regads,
Andy
Post #142789
Posted Tuesday, November 16, 2004 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2005 9:20 AM
Points: 3, Visits: 1

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 ??

Post #146558
Posted Thursday, March 24, 2005 3:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:45 AM
Points: 326, Visits: 244
Has anyone been able to make the "Importance" or "Priority" work using CDOSYS ?


Post #170013
Posted Tuesday, May 10, 2005 9:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 18, 2010 11:45 AM
Points: 14, Visits: 4

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!

Post #181106
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse