send mail from SQL 2K STD Edition.

  • Hi all,

    I have one server that is on SQL 2000 Std edition. I want to get email from server if the job fails or something else fails. But there is no SQL mail running also. How can i do that to get message for the job fail!!

     

    Thanks

  • I use this to notify me of a successful job. Just call it on Job Fail in your DTS package. I use a slightly different one that I pass a parameter to no my job fail, so I call it with a different parameter depending on which part of the job failed.

    Hope it works Ok for you.

    CREATE PROCEDURE [dbo].[SendSuccessMail]

    --Adapted from a Microsoft KnowledgeBase article Jan 16th 2006.

    -- @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 @From varchar(100) --origninally passed as parameter above. We want to hard-code it.

    Declare @To varchar(100) --origninally passed as parameter above. We want to hard-code it.

    Declare @Subject varchar(100) --origninally passed as parameter above. We want to hard-code it.

    Declare @Body varchar(4000) --origninally passed as parameter above. We want to hard-code it.

    Declare @iMsg int

    Declare @hr int

    Declare @source varchar(255)

    Declare @description varchar(500)

    Declare @output varchar(1000)

    Set @From = 'put the from mail address here'

    Set @To = 'put the to mail address here'

    Set @Subject = 'put the subject here'

    Set @Body = 'put the body of the msg here'

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

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

    IF @hr 0 BEGIN

    print 'sp_OACreate failed'

    END

    --***************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', 'put your SMTP server here'

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

    IF @hr 0

    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

    END

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

    -- Clean up the objects created.

    send_cdosysmail_cleanup:

    If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it

    BEGIN

    EXEC @hr=sp_OADestroy @iMsg

    END

    ELSE

    BEGIN

    PRINT ' sp_OADestroy skipped because @iMsg is NULL.'

    RETURN

    END

    GO

  • getting Error:

     Source: CDO.Message.1

     Description: The transport failed to connect to the server.

  • SQLRep,

    did you put the name of your SMTP server instead of words 'put your SMTP server here' in Neil's script?  This message is coming up when the script can not find SMTP server. If you did put the SMTP server name there make sure you qualified it with the domain name. Also make sure the Firewall on port 25 is open from your SQL Server to SMTP server. We do get this message with SMTP mail when the same script runs fine on our site and would not run from the computer on another site because the Firewall is not open to this SMTP server from that computer.

    Regards,Yelena Varsha

  • Hi all,

    I am using SQLAgent mail with Outlook 2000. Configure Outlook to use internal exchange server for sending mails by setting up Operator(s).

    If Outlook 2000 is out of your budget, there is an option - using Outlook express, as suggested by one of the forum topic.

     

    Hope this help!

  • Yelena is spot on, it's either an incorrect server name or Port 25 not opened.

Viewing 6 posts - 1 through 5 (of 5 total)

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