The results of my query has 701 rows. but when executing the msdb.dbo.sp_send_dbmail. i am receiving the email in excel sheet but all the rows are not displaying in the excel sheet only top 40 rows are getting displayed. The data is missing in excel sheet.
My query is as below:
This problem does not appear to be related to any data or configuration issues. The attachments appear to be truncated before email request is forwarded to SMTP server, snipping the file somewhere in the middle of the record. If the problem was related to some sort of mysterious row count limitation then the files would be truncated at the end of the last record, not in the middle. Running the following query will show some information about the sent mail items:
The data in filesize and actual_attachment_size columns should match of course, but it is a good idea to confirm. In case if the reported filesize numbers are close to about 65,000 then the problem is at the source (msdb procedure sending email), not any configuration. Configuration may be checked by running the following query:
Default is 1,000,000 bytes (comes to about 976 KB or a bit less than 1 MB).
I believe that the problem is related to the sp_send_dbmail procedure truncates attachments to 64 KB bug, which was already fixed by Microsoft about 3 years ago. Here is the quote from the Symptoms section:
According to this article, the issue was fixed in cumulative update 4 (CU4) for SQL Server 2014 which was released on October 21, 2014, build number 12.0.2430.0. If the SQL Server in question is indeed 2014 and its build number is smaller than 12.0.2430.0, then the cumulative update 4 or higher, and better still the service pack need to be applied to resolve this issue with attachments truncation.
Hope this helps.
answered Nov 30, 2017 at 01:53 PM
answered Nov 29, 2017 at 07:32 AM