December 1, 2005 at 9:59 am
Hi experts,
After we replaced our old server, we changed the SQL server name.
Few days ago, 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'.
This is what I did so far:
1. I have recreated this sp but it doesn't help.
2. the two following TransactSQL show different results:
a) 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
b). without attachment
master..xp_sendmail
@recipients = 'xxx@company.com',
@subject = 'test'
-- ******************************
-- @attachments = 'c:\test.txt',
-- ******************************
-->Mail sent
3. I also stop/start SQL Mail: exec master.dbo.xp_stopmail and exec master.dbo.xp_startmail
WHY am I NOT ABLE to send emails WITH attachments?
------------------------------------------------------------
here is the sp:
----------------------------------------------------------------
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@companyname.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
December 1, 2005 at 10:19 am
-What ServicePack are you on? Please post the result for Select @@version
-Is there a stack dump in SQL Server Error Logs? Can u post it here?
-Can u also post SQLAgent.out file?
-Do u have Fiber Mode set to ON?
-Run DBCC CHECKDB on MSDB and check for errors.
-Try running SQL Server from command prompt using sqlservr.exe -c as a console application and then try to run the xp_sendmail with attachment from Query Analyzer. Check for any errors? Does SQL Server hang in between?
-Make sure the attachment is in the right location that u specify.
-Can u send email attachments from your outlook client which is running on SQL Server?
December 2, 2005 at 1:09 am
Could it be that the service account that the sp is running under does not have permissions to the file? Try:
SET @mailattach = '\\DWAPP\InSight\Exports\Alerts\ISAlerts.csv'
DECLARE @rows int, @err int, @files int
-- Get the list of files
IF (SELECT OBJECT_ID('tempdb..#FileList')) > 0
EXEC('DROP TABLE #FileList')
CREATE TABLE #FileList (FileName varchar(260), depth int, isfile int)
INSERT INTO #FileList EXEC master..xp_dirtree @mailattach, 1, 1
SELECT @rows = @@ROWCOUNT, @err = @@ERROR, @files = 0
SELECT @files = COUNT(FileName) FROM #FileList WHERE FileName LIKE '%.txt'
IF @files = 0
PRINT char(9)+'No Import file to process - rows:'+CONVERT(varchar,@rows)+' error:'+CONVERT(varchar,@err)
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply