November 29, 2005 at 1:47 pm
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
November 29, 2005 at 11:52 pm
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
November 30, 2005 at 8:41 am
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