June 20, 2013 at 4:00 pm
We are using Database Mail on SQL Server 2008 R2 to send results of queries as jobs using SQL Server Agent. We are using an MS exchange mail server (mail.mycompany.com) & the default port 25. All emails that have the query results in the body of the email work fine. However, those that use @attach_query_result_as_file = 1 or those that have nothing in the body of the email fail with this error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2013-06-20T13:54:39). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )
The event viewer & error logs on the mail server provide no data, which leads us to believe that the emails are not making it to the mail server. All firewalls and virus protection software have been checked and are not configured to stop emails with attachments from being sent.
The following code from the sproc in question is working with no issues whatsoever with the exact set up described above except it is hosted on SQL Server 2005 (please note that the value for @RecipientList is passed as a parameter to the sproc & that we've added values for @body & @body_format to ensure there is something in the body of the email. Also the sproc works fine and sends the email with the attachment from management studio. It is when it is called from the SQL Server Agent job that it fails):
DECLARE @sql nvarchar(MAX)
DECLARE @File varchar(1000)
DECLARE @SubjectLine varchar(200)
SET @File = 'Dupes.txt'
SET @SubjectLine = 'Active devices with duplicate MfgSerialNumbers'
SET @sql = 'SET NOCOUNT ON;
DECLARE @Dupes TABLE
(
MfgSerialNumber varchar(100)
)
INSERT INTO @Dupes
SELECT MfgSerialNumber
FROM Devices
WHERE Active = 1
AND MfgSerialNumber <> ''NO SERIAL NUMBER FOUND''
GROUP BY MfgSerialNumber
HAVING ( COUNT(MfgSerialNumber) > 1 )
ORDER BY MfgSerialNumber;
DECLARE @data TABLE
(
Customer varchar(100)
,Campus varchar(60)
,Bldg varchar(75)
,Floor varchar(15)
,CostCenter varchar(50)
,Dept varchar(75)
,Area varchar(100)
,Location varchar(100)
,AuxBarcode varchar(10)
,MfgSerialNumber varchar(50)
,DeviceID int
,Active varchar(10)
,Mfg varchar(30)
,Model varchar(60)
)
INSERT INTO @data
SELECT
TOP (100) PERCENT
dbo.Customers.Name AS Customers,
dbo.Campuses.Name AS Campus,
dbo.Buildings.Name AS Building,
dbo.Floors.Floor,
dbo.Departments.DepartmentNumber,
dbo.Departments.DepartmentName,
CASE
WHEN
(CHARINDEX(''\'', dbo.Locations.LocationDescription)) = 0
THEN
''''
ELSE
LTRIM(RTRIM(LEFT(dbo.Locations.LocationDescription, (CHARINDEX(''\'', dbo.Locations.LocationDescription) - 1))))
END AS Area,
CASE
WHEN
(CHARINDEX(''\'', dbo.Locations.LocationDescription)) = 0
THEN
dbo.Locations.LocationDescription
ELSE
LTRIM(RIGHT(dbo.Locations.LocationDescription, (LEN(dbo.Locations.LocationDescription) - (CHARINDEX(''\'', dbo.Locations.LocationDescription)))))
END AS Location,
dbo.Devices.AuxBarcode,
dbo.Devices.MfgSerialNumber,
dbo.Devices.DeviceID,
CASE WHEN Devices.Active = 0 THEN ''FALSE'' ELSE ''TRUE'' END AS Active,
dbo.DeviceMfgs.MfgName,
dbo.DeviceModels.ModelName
FROM dbo.Customers INNER JOIN
dbo.Campuses ON dbo.Customers.CustomerID = dbo.Campuses.CustomerID INNER JOIN
dbo.CampusBusinessUnits ON dbo.Campuses.CampusID = dbo.CampusBusinessUnits.CampusID INNER JOIN
dbo.BusinessUnits ON dbo.CampusBusinessUnits.BusinessUnitID = dbo.BusinessUnits.BusinessUnitID INNER JOIN
dbo.Buildings ON dbo.Campuses.CampusID = dbo.Buildings.CampusID INNER JOIN
dbo.Floors ON dbo.Buildings.BuildingID = dbo.Floors.BuildingID INNER JOIN
dbo.Departments ON dbo.Campuses.CampusID = dbo.Departments.CampusID
AND dbo.CampusBusinessUnits.CampusBusinessUnitID = dbo.Departments.CampusBusinessUnitID INNER JOIN
dbo.SubDepartments ON dbo.Departments.DepartmentID = dbo.SubDepartments.DepartmentID INNER JOIN
dbo.Devices ON dbo.SubDepartments.SubDepartmentID = dbo.Devices.SubDepartmentID INNER JOIN
dbo.Locations ON dbo.Devices.LocationID = dbo.Locations.LocationID
AND dbo.Floors.FloorID = dbo.Locations.FloorID INNER JOIN
dbo.DeviceMfgs ON dbo.Devices.DeviceMfgID = dbo.DeviceMfgs.DeviceMfgID INNER JOIN
dbo.DeviceModels ON dbo.Devices.DeviceModelID = dbo.DeviceModels.DeviceModelID INNER JOIN
dbo.DeviceTypes ON dbo.DeviceModels.DeviceTypeID = dbo.DeviceTypes.DeviceTypeID INNER JOIN
dbo.PrintTechnology ON dbo.DeviceModels.PrintTechnologyID = dbo.PrintTechnology.PrintTechnologyID LEFT OUTER JOIN
dbo.NetworkInterfaces ON dbo.Devices.DeviceID = dbo.NetworkInterfaces.DeviceID
WHERE (dbo.Customers.Active = 1)
AND (dbo.Customers.CustomerID <> 81)
AND (dbo.Campuses.Active = 1)
AND (dbo.Devices.Active = 1)
AND (dbo.Devices.MfgSerialNumber IN (SELECT MfgSerialNumber From @Dupes))
ORDER BY dbo.Customers.Name, dbo.Devices.MfgSerialNumber;
select * From @data;'
EXEC msdb.dbo.sp_send_dbmail
@recipients = @RecipientList,
@query_result_separator = '' ,
@subject = @SubjectLine,
@body = 'end of message',
@body_format = 'text',
@profile_name ='appropriate profile name that works with other emails',
@query = @sql,
@attach_query_result_as_file = 1,
@query_attachment_filename = @File,
@query_result_header = 0,
@query_result_no_padding = 1,
@execute_query_database='appropriate db name'
I've gone through a good number of the posts regarding Database Mail on this site but did not come across anything like this problem. I apologize if a solution has been posted previously.
Thank you in advance.
June 20, 2013 at 4:17 pm
Requested action not taken: message refused
I think that is the answer, the server refuses to take the message. My first guess would be that the message is too big. You can set max message size in both SQL AND Exchange but the rule is SQL max <= Exchange max..
CEWII
June 20, 2013 at 4:22 pm
Thank you for your reply. If that is the issue then it is somewhat mystifying because the same exchange server currently receives the email from Database Mail on SQL Server 2005 with no problems. The email attachments are .txt files and are in the range of 55 kb or so.
June 20, 2013 at 4:31 pm
The other part that caught my attention was:
Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused).
Do all emails come from the same email address and/or use the same login credentials? Does it use trusted connectivity or no security? And you have indicated this only sometimes happens? Is this query the only one that fails?
I just thought of something.. Exchange verifies that a mailbox exists for mailboxes it hosts so if you try to send to an email address that doesn't exist I can see this happen. Verify that all the recipients exist and that their names are spelled EXACTLY correct.
CEWII
June 20, 2013 at 4:39 pm
All emails come from the same email address and there is only 1 profile each set up on the 2005 and 2008 machines. Anonymous authentication is used in the Database Mail setup. I am logged into both SQL Server machines as sys admin using Windows authentication. The exact same exchange account is used for both, sending to & from the same exact email addresses. And on the 2008 machine any code the uses @attach_query_result_as_file = 1 is failing. All sprocs that write the results of the query to the body of the email message are working flawlessly on the 2008 machine.
Again thanks for reply.
June 20, 2013 at 5:09 pm
Boy, I'm running out of ideas..
So basically it works in 2008 but not 2005.
And you have verified the recipients are exactly the same? One recipient or a list? If its a list does it use the correct delimiter?
CEWII
June 20, 2013 at 5:21 pm
Thanks for your effort! No, it is the opposite. It has been working flawlessly for years with the same setup on 2005. We are migrating to 2008 (finally) and we need to get these jobs working there. And as mentioned as long as the query results are in the body of the email it works on the 2008 machine.
The sending email address & recipients are the same. It is a group email address for all employees that need to receive the rpt. The members of that email address haven't changed for quite some time.
Thanks.
June 20, 2013 at 5:26 pm
Ok, stupid question, what is the patch level of your SQL 2008 install? Maybe there was a bug that was fixed.
CEWII
June 20, 2013 at 5:42 pm
All latest patches have been applied. This is a fresh vm just put online in the last 2-3 weeks. All other services are working with no issues.
June 20, 2013 at 9:37 pm
ok, all patches applied.
same process that worked in 2005 but not in 2008
same servers
same setup
You can send email in 2008 except when you @attach_query_result_as_file =1
The sp_send_dbmail returns successfully
That about everything?
I'm reaching but maybe try @query_attachment_filename = NULL
Try @append_query_error = 1
Are there any retries setup?
CEWII
June 20, 2013 at 9:57 pm
Tried both suggestions, first @append_query_error = 1, same result. Then changed file name to @query_attachment_filename = NULL and got the error message added twice to sysmail_log after executing the job. I have to say at this point that this is weird.
And yes you have the scenario correct, except what do you mean by "The sp_send_dbmail returns successfully"? No retries in code. Just those attempted by db mail.
Thanks very much.
June 20, 2013 at 10:04 pm
and BTW - when the sproc is executed from management studio & not called as sql server agent job, it works. Weird.
June 20, 2013 at 10:21 pm
bdanks 85818 (6/20/2013)
And yes you have the scenario correct, except what do you mean by "The sp_send_dbmail returns successfully"? No retries in code. Just those attempted by db mail.
I mean that the call to the sproc returns without error and the retries I meant are configured at the database mail level, retries, max attachment size and such are set at that same spot..
CEWII
June 20, 2013 at 10:30 pm
Hi,
could it be a access issue? Sql service and agent are running under different accounts?
June 20, 2013 at 10:33 pm
the sproc executes fine & sends the email with the attachment when called from management studio so I would say the sproc and the call to sp_send_dbmail within it are fine. When executing the job from sql server agent, the error mentioned in original email occurs.
Increased retry attempts to 10. No change.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply