Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database Mail failing with attachment Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 4:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1465975
Posted Thursday, June 20, 2013 4:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1465981
Posted Thursday, June 20, 2013 4:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1465982
Posted Thursday, June 20, 2013 4:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1465984
Posted Thursday, June 20, 2013 4:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1465986
Posted Thursday, June 20, 2013 5:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1465994
Posted Thursday, June 20, 2013 5:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1465997
Posted Thursday, June 20, 2013 5:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Ok, stupid question, what is the patch level of your SQL 2008 install? Maybe there was a bug that was fixed.

CEWII
Post #1465998
Posted Thursday, June 20, 2013 5:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 4:02 PM
Points: 12, Visits: 55
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.
Post #1466007
Posted Thursday, June 20, 2013 9:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1466020
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse