none
Database Mail failing with attachment

    Question

  • 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):

    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'

    All emails come from the same email address and there
    is only 1 profile set up on both 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. Again 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. And all are working flawlessly on SQL Server 2005 machine.


    Thank you in
    advance.

    Friday, June 21, 2013 12:04 AM

All replies