SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Mail failing with attachment


Database Mail failing with attachment

Author
Message
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 56
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.
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25146 Visits: 5314
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
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 56
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.
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25146 Visits: 5314
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
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 56
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.
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25146 Visits: 5314
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
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 56
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.
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25146 Visits: 5314
Ok, stupid question, what is the patch level of your SQL 2008 install? Maybe there was a bug that was fixed.

CEWII
bdanks 85818
bdanks 85818
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 56
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.
Elliott Whitlow
Elliott Whitlow
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25146 Visits: 5314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search