Results 1 to 2 of 2

Thread: Cdosys

  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: Cdosys

    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/de..._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/de..._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
    Last edited by hellhound; 02-10-04 at 00:18.

  2. #2
    Join Date
    Feb 2004
    Posts
    10

    bump?

    Anyone? Bueller?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •