Cdosys
Feb 9, 2004
I am trying to send job status information using smtp via cdosys. I have it emailing but I want to pass the servername and jobname in the email. Can anyone assist?
***oops, here is the SP and the command ran from the job steps;;;
************************************************** *****
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
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
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
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', 'smtpserver'
-- 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
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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
************************************************** ****
declare @Body varchar(4000)
select @Body = 'Job Succeeded'
exec sp_send_cdosysmail 'me@you.com','you@me.com','servername Jobname',@Body
Thanks
View 1 Replies
ADVERTISEMENT
Feb 27, 2006
Hi,
I am using the following stored procedure to send a mail with attachment. But the mail is sent without the attachment. Can anyone help me?
CREATE PROCEDURE DBO.sp_Send_Mail_test(
@p_From as nvarchar(50),
@p_To as nvarchar(50),
@p_Subject as nvarchar(255),
@p_Body as varchar(1000),
@p_CC as text = null,
@p_BCC as text = null,
@p_Attachment varchar(500)=null
)
AS
Declare @Message int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
EXEC @hr = sp_OACreate 'CDO.Message', @Message OUT
EXEC @hr = sp_OASetProperty @Message, 'From',@p_From
EXEC @hr = sp_OASetProperty @Message, 'To', @p_To
EXEC @hr = sp_OASetProperty @Message, 'Subject', @p_Subject
EXEC @hr = sp_OASetProperty @Message, 'TextBody', @p_Body
EXEC @hr = sp_OAMethod @Message, 'CDO.Message.Attachment.Update', Default, @p_Attachment
If @p_CC is not null
BEGIN
EXEC @hr = sp_OASetProperty @Message, 'CC',@p_CC
END
If @p_BCC is not null
BEGIN
EXEC @hr = sp_OASetProperty @Message, 'BCC',@p_BCC
END
EXEC @hr = sp_OAMethod @Message, 'Send', NULL
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description out
EXEC @hr = sp_OADestroy @Message
IF @hr <> 0
BEGIN
SELECT hr=convert(varbinary(4),@hr), Source=@source, Description=@description
RETURN
END
Regards,
Bharathram G
View 1 Replies
View Related
Feb 8, 2005
This was a P A I N to get working. Maybe someone else here is sending email and could use it.
A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...
Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress, sSubject, sBody, boolReadReceipt, intImportance )
'on error resume next
Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
dim cdoMessage, cdoConfiguration
Set cdoConfiguration = Server.CreateObject ("CDO.Configuration")
' Outgoing SMTP server
With cdoConfiguration
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Fields.Update
End With
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
' Update the CDOSYS Configuration
SET .Configuration = cdoConfiguration
.BodyPart.charset = "unicode-1-1-utf-8"
IF boolReadReceipt Then
.Fields(cdoDispositionNotificationTo)= sFromAddress
.Fields(cdoReturnReceiptTo)= sFromAddress
End If
' Set the Importance: 0:Low, 1:Normal, 2:High
.Fields("urn:schemas:httpmail:importance").Value= intImportance
.Fields.Update
.From= sFromAddress
.ReplyTo= sFromAddress
.To= sToAddress
.Cc= sCcAddress
.Bcc= sBccAddress
.Subject= sSubject
.Textbody= sBody
.Send
End With
Set cdoMessage = Nothing
Set cdoConfiguration = Nothing
End Sub
View 6 Replies
View Related
Sep 8, 2004
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/defaul...;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
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/d...s_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
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/d...n_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', 'MailServerName'
-- 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
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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
View 3 Replies
View Related
Sep 8, 2004
Hi,
Im using a stored procedure to send an email using CDOSYS. Im using the code i got here
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
I can successfully send an email, but would like to know how i can add additional fields like setting the importance or priority of the how email. How would i change this stored procedure to do this.
here is the code...
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
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
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
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', 'MailServerName'
-- 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
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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
View 2 Replies
View Related
May 18, 2004
Hi,
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
Thanks,
Kim
View 3 Replies
View Related
Feb 26, 2007
I currently have a simple cdosys email task that has been scheduled to send a simple email from ssis. The email is sent using an activex script in a "SQL 2000 DTS Package Task". When executed manually, the email is sent ok. When scheduled (and run under our SQL agent account), it fails. Can anyone point me in the right direction? Is this a permissions issue?
'-- this script seems to cause problems, but only when scheduled --
dim mailer
set mailer = CreateObject("CDO.Message")
dim cdoconfig
const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
set cdoconfig = CreateObject("CDO.Configuration")
with mailer
set .Configuration = cdoconfig
.BodyPart.charset = "unicode-1-1-utf-8"
.BodyPart.ContentTransferEncoding = "quoted-printable"
.Fields("urn:schemas:httpmail:importance").Value = 2
.Fields.Update
.Subject = "Notification"
.From = "donotreply@test.com"
.TextBody = "TEST"
.Bcc = "someone@test.com"
.Send
end with
'-------------
Also, since I have several DTS packages that are similar, I'd like to keep these packages in the SQL 2000 dts format, instead of converting them into SSIS format and using database mail.
Any help would be appreciated.
View 2 Replies
View Related