The sp can''t send email after we changed the server name

  • Hi experts,

    After we replaced our old server, we changed the server name from AMKDW to SVDW.

    Today I was notified that one send email stored procedure doesn't distribute email anymore after the server is replaced. I tried to run the sp in SQL Query Analyzer and the message says 'Mail Status:1'.

    What could go wrong?  I have recreated this sp but it doesn't help.

    I tested the email by:

    EXEC dbo.xp_sendmail

         @recipients = 'xxx@mycompany.com',

         @subject = 'Test Sending Email'

    And it's working fine.

    Please help. Thank you.

     

     

    ----------------------------------------------------------------

    CREATE   PROCEDURE [Alerts_Email] AS

    BEGIN

    DECLARE @email_id varchar(50)

    DECLARE @RecCount INT

    DECLARE @MyRecCount INT

    DECLARE @at_pos INT

    DECLARE @email_prefix varchar(20)

    DECLARE @status INT

    DECLARE @mailsub varchar(50)

    DECLARE @mailmessage varchar(400)

    DECLARE @mailid varchar(50)

    DECLARE @mailattach varchar(50)

    SET @mailsub = 'Daily DSS Help Desk IS Alerts'

    SET @mailmessage = 'The attached alert is distributed as part of the Daily Store Alerts.

    Please review the information carefully.

    For further information Contact Carol'

    Set @mailid = 'DSSHelpDesk@ameriking.com'

    SET @mailattach = '\\DWAPP\InSight\Exports\Alerts\ISAlerts.csv'

    EXEC @status = master.dbo.xp_sendmail @recipients = @mailid,

     @subject = @mailsub,

            @message = @mailmessage,

     @attachments = @mailattach,

     @no_output = 'TRUE'

    PRINT 'Mail Status:' + convert(varchar,@Status)

    END

    GO

  • You did verify that Outlook can still connect using the default profile, to the mail server when logged in as the SQL Server service and SQL Server Agent service accounts, right?

    You did verify that the SQL Mail service and SQL Server Agent SQL mail connections, right? You did verify that Operators can receive email by Testing, right?

    You did stop and start the MSSQLSERVER SQL Mail "service", right?

    I add the following for critical scheduled stored procedures that email:

    DECLARE @Err int

    -- Test to see if the SQL Mail "service" is available

    -- Otherwise raise the error that will terminate the connection

    EXEC @Err = master.dbo.xp_stopmail

    IF @Err <> 0

     RAISERROR('xp_stopmail: Failed to stop SQL Mail session.',20,1) WITH LOG

    ELSE

     EXEC master.dbo.xp_startmail

    The advantage here is that if the MAPI email connection cannot be made the sheduled job will terminate and notify me, instead of hanging until someone finds that it and all other emailing stored procedures have also hung.

    If you do not want the connection (SPID) to terminate, then change 20 above to 19. You will still get the event, but the connection will not be terminated.

    The trouble is that there is almost no way to monitor whether the MSSQLSERVER MAPI connection (SQL Mail) is up without the attempt to stop it and of course stopping it when it is attempting to be used by other connections also causes conflicts. So use this with caution.

    The advantage is that this "service" is restarted once a day, which helps minimize SQL Mail problems.

    Andy

  • Thank you Andy.  I did test the connection and stop/started the service.

    But I still not able to send attachment.

    the two following TransactSQL show different

    results:

    1. with attachment

    master..xp_sendmail

    @recipients = 'xxx@company.com',

    @subject = 'test',

    -- ******************************

    @attachments = 'c:\test.txt',

    -- ******************************

    -->18025 level 16 state 1 line 0

    xp_sendmail failed with mail error 0x80004005

    2. without attachment

    master..xp_sendmail

    @recipients = 'xxx@company.com',

    @subject = 'test'

    -- ******************************

    -- @attachments = 'c:\test.txt',

    -- ******************************

    -->Mail sent successful

    I also did : exec master.dbo.xp_stopmail and exec master.dbo.xp_startmail

    why am I not able to send emails with attachments?

Viewing 3 posts - 1 through 2 (of 2 total)

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