SQLServerCentral / Discuss Content Posted by Desiree Harris / Article Discussions / Article Discussions by Author / Sending SQL Notifications with CDOSYS / Latest Posts

RE: Sending SQL Notifications with CDOSYS

van800 — Wed, 01 Apr 2009 14:36:17 GMT

I have added several propertities to use google smtp as smtp serverBut I get the error "Description: The server rejected the sender address. The server response was: 530 5.7.0 Must issue a STARTTLS command first. 28sm166958fxm.4"It is rather strange cause I enabled SMTPUseSSLDoes any one have ideas? EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendUsing").Value','2'EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.googlemail.com'EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusingport").Value', '465'EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True-- SMTPAuthenticateEXEC @hr = sp_OASetProperty @iMsg,'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value',1--0 is do not authenticate--1 is Use basic (clear-text) authentication. The configuration--sendusername/sendpassword or postusername/postpassword fields are used--to specify credentials.--2 Use NTLM authentication. The current process security context is used--to authenticate with the service. Integrated security.EXEC @hr = sp_OASetProperty @iMsg,'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUsername").Value','myAdr@gmail.com'EXEC @hr = sp_OASetProperty @iMsg,'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value','myPass'

RE: Sending SQL Notifications with CDOSYS

Emito — Thu, 15 Jan 2009 10:44:00 GMT

Thanks You!Works very well.Regards, Emito.:D

RE: Sending SQL Notifications with CDOSYS

dseph2000 — Fri, 01 Feb 2008 12:59:48 GMT

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.aspxI'll probably add more to the above blog entry later....Thanks,Dan

RE: Sending SQL Notifications with CDOSYS

Don Staten — Wed, 02 Nov 2005 10:15:00 GMT

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

RE: Sending SQL Notifications with CDOSYS

Spencer-206914 — Wed, 01 Jun 2005 16:21:00 GMT

double bump

RE: Sending SQL Notifications with CDOSYS

Spencer-206914 — Wed, 11 May 2005 09:11:00 GMT

Anyone?

RE: Sending SQL Notifications with CDOSYS

Spencer-206914 — Tue, 10 May 2005 09:37:00 GMT

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!

RE: Sending SQL Notifications with CDOSYS

Tom Blaylock — Thu, 24 Mar 2005 15:24:00 GMT

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

RE: Sending SQL Notifications with CDOSYS

B Thomas — Tue, 16 Nov 2004 12:25:00 GMT

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

RE: Sending SQL Notifications with CDOSYS

andyobl — Thu, 21 Oct 2004 09:04:00 GMT

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

RE: Sending SQL Notifications with CDOSYS

JBZ — Thu, 09 Sep 2004 14:19:00 GMT

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

RE: Sending SQL Notifications with CDOSYS

kmarshall — Thu, 09 Sep 2004 08:40:00 GMT

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

Just wanted to say thanks.

RE: Sending SQL Notifications with CDOSYS

itboy — Thu, 09 Sep 2004 06:47:00 GMT

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'

RE: Sending SQL Notifications with CDOSYS

kenwallacedesign — Thu, 09 Sep 2004 04:24:00 GMT

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 2Khttp://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

RE: Sending SQL Notifications with CDOSYS

Clayton Groom — Fri, 27 Aug 2004 01:05:00 GMT

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

Sending SQL Notifications with CDOSYS

Desirée B. Harris — Wed, 25 Aug 2004 18:35:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist