asked
I am trying to send the results of sqlquery to an email in excel sheet using msdb.dbo.sp_send_dbmail
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: DECLARE @tab char(1) = CHAR(9) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'admin', @recipients = 'xxxxxxxxxxxx', @query = 'SET NOCOUNT ON EXECUTE [TMS].[dbo].[SearchTickets] @CustomerId="C5C2594A-29A1-47FC-A284-2F42A1F5D4D3" ,@ProjectId=null ,@CustomerReferenceNumber=null ,@ProblemDetails=null ,@AssignedTo=null ,@AssignedTeamId=null ,@CurrentStatus=null ,@RespondedBy=null ,@ApplicationId=null ,@PriorityId=null ,@DetailedCategoryId=null ,@SubCategoryId=null ,@TicketNo=null ,@CallTypeCode=null ,@CallSourceCode=null ,@ResolvedBy=null ,@ContactName=null ,@Submitter=null ,@ProblemSummary=null ,@AssignedDateGreater=null ,@AssignedDateLesser=null ,@ResolvedDateGreater=null ,@ResolvedDateLesser=null ,@IsResponseOverDue=null ,@IsResolutionOverDue=null ,@Country=null', @subject = 'Testing', @attach_query_result_as_file = 1, @query_attachment_filename='filename.xls', @query_result_separator=@tab, @query_result_no_padding=1, @query_result_header = 1, @query_result_width = 32767
@dantenan If you manually copy the results of a query and paste it in excel sheet then the size of the file is much, much smaller than the size of the file produced by **msdb.dbo.sp\_send\_dbmail** procedure because of the unfortunate file name, or rather extension, that you use. The extension is XLS (old Excel 97-2003 format), these files are far greater in size than their respective XLSX counterparts. It looks like you have a correct setting for @query\_result\_no\_padding=1 which should ***overwrite the @query\_result\_width parameter*** according to documentation, thus reducing the file size, but the XLS might be a poor choice. Please try to change it to XLSX. Database mail default limit for all attachments is 1 MB and it is possible that you exceed it.
How big is the file you're receiving? sp_send_mail has a file size limit.
@Oleg regarding the default limit of 1MB. I have even changed the maximum file size limit to 10 mb in database mail configuration wizard. And when I change the extension type to .xlsx, after receiving the email, I am unable to open the file in excel it is throwing a error saying file might be corrupted. The file i received through email when i keep the extension as .xls and .xlsx is 64 kb same for both extensions
[This is the file i am receiving through email with .xls extension[link text][1] [1]: /storage/temp/4389-manualy-copied-results.xlsx I have attached 2 excel sheets one is manually copied and the other one is received through email. I want the email copy also to have all the rows of the query result as available in manually copied excel sheet
![alt text][1] [1]: /storage/temp/4395-messages-menu.png PFA
@dantenan I just tested some sample data on my end I have no problems with sending out email with attachment including thousands of rows in the file. It really looks like your problem is related to the data and the procedure, so please try the following: - In the procedure text, add the line reading **set nocount on** after the line reading BEGIN - In the procedure text, change the data type of @vSQL to **nvarchar(max)** from **varchar(8000)**, so make the line read DECLARE @vSQL nvarchar(max) - Toward the bottom of procedure text, please remove (or comment out) the line reading **print @vSQL** - Change the line reading **Execute (@vSQL)** to read **exec sp_executesql @statement = @vSQL** - You can change @query_result_header = 0 to be set to 1 to get your headers back in, because the removing PRINT statement already helps to get rid of that query text. Please let me know if the above works.
@dantenan Also, and this one is important: it looks like the column TicketDetail.TICKET_PROBLEM_SUMMARY values have line breaks in some rows, which will cause the problem with CSV files (the values of other columns will shift). The resulting row MUST NOT be returned as varchar(8000) even if it is designed as such, it should be converted to varchar(max). For example, you can replace the line breaks with spaces to fix the columns' shifting problem in some rows and cast the column as varchar(max) by changing the text in the procedure currently reading TicketDetail.TICKET_PROBLEM_SUMMARY AS ProblemSummary, to read this instead: cast(ltrim(rtrim(replace(replace(TicketDetail.TICKET_PROBLEM_SUMMARY, char(13), ''''), char(10), '' ''))) as varchar(max) ) AS ProblemSummary If there are any other varchar(8000) columns, please cast them to varchar(max) as well.
@olego first of all thanks for your detailed information step by step. I was able to implement the steps you mentioned as above and by doing this i was able to get rid of select query at the first row of the excel sheet. but the rows are still 46 only out of 701. i will attach the excel please go through it.
@dantenan This is interesting. The data in both spreadsheets (with and without select statement included) has practically the same number of characters (32,768 in today's file and 32,781 in yesterday's file). The size is reported as 65 KB for each file, which probably means that the procedure generates the files in Unicode. Something causes the files to be truncated once the 32,767 is reached which is your value set for query result width. It is ***almost as though the engine believes that the query results consists of one row and one column***, and that column is chopped off once the limit is reached. Just to confirm that this is the case, please put the print back in and execute the procedure manually. This will yield the select statement which is then fed to dynamic execution. Now, take this (static at this time) select statement and provide it as the value of the @query parameter to the procedure sending email. I highly suspect that this will produce the file with all 701 rows included in it. If what I suspect is correct, this exercise will show the way to cure the problem.
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: select ma.mailitem_id, mi.recipients, mi.[subject], mi.query, [filename], ma.filesize, datalength(ma.attachment) actual_attachment_size, cast(ma.attachment as nvarchar(max)) attachment_text from msdb.dbo.sysmail_attachments ma inner join msdb.dbo.sysmail_mailitems mi on ma.mailitem_id = mi.mailitem_id where filename = '[file_name_goes_here]'; 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: select paramname, [description], paramvalue / 1024 size_in_kb from msdb.dbo.sysmail_configuration where paramname = 'MaxFileSize'; 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][1] bug, which was already fixed by Microsoft about 3 years ago. Here is the quote from the Symptoms section: > Assume that you use Database Mail and sp_send_dbmail stored procedure with the @query and @attach_query_result_as_file option in Microsoft SQL Server 2014. The stored procedure run a query and set the result-set as an attachment of the email. When the query has a large result-set, the attachment is truncated to 64 kilobyte (KB) maximum. 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. Oleg [1]:
https://support.microsoft.com/en-us/help/3004195/fix-sp-send-dbmail-stored-procedure-truncates-email-attachments-to-64
@Oleg Yes finally able to see all the 701 rows in the excel sheet after upgrading the SQL Server 2014 with CU4. Below are the links to upgrade CU4
https://support.microsoft.com/en-us/help/2999197/cumulative-update-package-4-for-sql-server-2014
@Oleg I have attached my excel sheet please go through it. Even after changing TicketDetail.TICKET_PROBLEM_SUMMARY AS ProblemSummary, to read this instead: cast(ltrim(rtrim(replace(replace(TicketDetail.TICKET_PROBLEM_SUMMARY, char(13), ''''), char(10), '' ''))) as varchar(max) ) AS ProblemSummary I still see the column getting across other rows. I also want to fix the below things in the excel sheet, could you please let me know is there a way to fix these things 1) How can I adjust the size of each column as per the data available, because every time i open the excel sheet I am adjusting the size of the column manually to read the complete line in each column. 2) And at the bottom of the excel sheet there is a message displaying "701 rows executed" I would also like to get rid of this message. Thanks and Regards
[link text][1] [1]: /storage/temp/4405-gil.zip PFA of excel sheet
@oleg
https://stackoverflow.com/questions/35309739/define-proper-width-in-csv-generated-by-sp-send-dbmail Please see the above link this is exactly what is happening in the excel file
@dantenan In order to get rid of the "701 row(s) affected" message you are going to have to use the ***SET NOCOUNT ON***, this will remove the message. As far as formatting is concerned then no, it is not possible with the send mail procedure. You call the procedure with instructions to use tab character as a field delimiter and then attach the results as a file. The file that the procedure generates and attaches is NOT a real Excel spreadsheet, it is merely the tab-delimited text which happens to be in the file with XLS extension courtesy of the value of the query attachment filename parameter. Setting the extension of the file to XLS does not make the file to magically become Excel file. Excel will still open such file but because the guts of the file are just a tab-delimited text values with no formatting, the file will end up not formatted, and it will take a single-click in the top left corner followed by double-click between A and B columns to auto-size all columns at once. Sending out properly formatted Excel files with the results of the stored procedure is possible, but not by the means of the msdb procedure used to send emails.
@dantenan Wrapping of the values to the next line ***is not*** caused by the **ProblemSummary** column, this one is already handled, but is caused by the **TICKET\_PROBLEM\_DETAIL** which you left out for some reason. I mean that if you have columns which might have line breaks in the values then you have to handle all of them in the similar fashion, just like you did with the **ProblemSummary**.
@Oleg Hi Oleg there are few columns in Excel sheet which displays the date and time like for example in my result set the column Ticket_ Resolution_Deadline will display as 2017-11-22 13:00:00.927 in sql server but the same column is displaying as 00:00.9 in excel sheet. Is there a way to fix it? Thanks and Regards, Dantenan
@dantenan The 00:00.9 is just the default formatting Excel applies when you force it to open the tab-delimited file as though it is Excel spreadsheet. The data in the same file, if you open it in Notepad, will still show the date and time just as it comes out of the query. Generally speaking any workaround you find to "fix" the problems with formatting are just temporary patches, they might or might not work. For example, the numeric values will display normally in most cases, but Excel might reformat some of them to scientific format (depending on the number), etc. The better solution would be to NOT use the msdb procedure to send out emails, but write a small SSIS package (or better still a small C# program) which will export the data to the proper Excel spreadsheet and then format it as needed, so that the final output has no problems with formatting/columns' sizing.
@Oleg Currently the only issue i am facing with msdb procedure to send the emails is the columns width of the generated .csv file. Currently after opening the .csv file i am manually extending each column to make it readable which I dont think will be a good practice. So based on your information does it mean if i use SSIS package to send the results results of an query as an Excel file to email, I will be able to fix the width of each column as per my requirement? Thanks and Regards,
@dantenan What I have done in the past in order to export nicely formatted data from database query is the following: - ACE provider expects the template file to already exist prior to pumping data into it so the first SSIS task is the simple script task to copy existing template (already formatted / sized) to the destination directory. - Next task is to pump the data into that Excel file. - Once the data is available, do necessary touch ups if needed using the script task. The best assembly I found to work with Excel spreadsheets is [EPPlus][1]. Technically, the spreadsheets can be manipulated with built-in .NET libraries, but EPPlus makes the job much easier (and number of lines of codes much, much smaller :) ) - The last (optional) task is to send out the file as an attachment in the email. Of course it goes without saying that all of the above can be accomplished with a very small C# program without any SSIS involvement. This way, the code may be called on demand (say, from website, so whenever the user performs the search, she immediately gets email with the search results, if this is what needs to happen). [1]:
http://epplus.codeplex.com/
@Oleg actually here the requirement is that the client should receive this email every day at a paticular time(which can be done by scheduling a job) without sending it manually. Actually we are able to generate same report with prefect column width in the application in front end with the help of C# code and also able to export the report to excel sheet from application. And after saving the report and we are daily mailing the report to client manually So to generate it automatically daily without someone sending the email manually i am doing this. Would like to know your inputs on this Thanks and Regards
@dantenan But if you already have all code written and it already works generating correctly formatted Excel spreadsheet then what prevents you from adding the code to send out email attaching the file which already exists? You can then schedule this code to run on whatever schedule you need by either rolling it out as windows service or as a scheduled task in windows task scheduler or by creating SQL Job with only one step (to execute the C# code or to still execute the msdb procedure without specifying the query but specifying existing formatted spreadsheet).
@Oleg that sounds great, if i can use the same code to generate email through sql. is it possible for your to share your personal contact or wats app number. i would like to speak to you directly on call and explain to you the exact scenario. it would be really helpfull. please consider it as a request. Thanks and Regards
[link text][1] [1]: /storage/temp/4414-storedprocedure.txt @Oleg as discussed this is the stored procedure. There is a parameter @CustomerID where i am trying to add multiple values. Thanks and Regards
@dantenan Because the statement is dynamic, there is not much to so to add the support for multiple customers. First, increase the size of the @CustomerId parameter from varchar(50) so it can accommodate list of customers. Once this this done, please find the line of code which needs to be modified. Currently, it is reading: if(@CustomerId '00000000-0000-0000-0000-000000000000' AND @CustomerId IS not NULL) BEGIN select @vSQL = @vSQL + 'dbo.Project.FK_CUSTOMER_ORG_ID = ''' + convert(varchar(36), @CustomerId) + '''' END The select statement needs to be modified to read this: select @vSQL = @vSQL + 'dbo.Project.FK_CUSTOMER_ORG_ID in (''' + replace(replace(@CustomerId, ', ', ','), ',', ''',''') + ''')' This will add support for multiple customer ID values, provided that they are passed as a comma-delimited list to the procedure. For example, 2 customers: 3EE99669-FC20-442E-81AA-0A58D3C84C9F,6692892C-E2A6-412F-89AD-CC058B5A4033 Also, the current procedure has a bug (if the @customerID is null then the procedure raises error instead of running for all customers).
18 People are following this question.
