0

I have innumerous reports that I send from SQL Server to people.

But for some reason there's this single one, with 10k+ rows, that I can't send via dbmmail.

I tried top 7000 and it worked but top 8000 still creates the file, I can open it, but I can't send via dbmail. No errors or alerts about SQL attachment size limit.

File is 1.5MB and I don't receive any error message. I changed only the attachment name to see if the code was wrong but it sent the new file (the smaller one).

It's not an attachment size error because I configured it.

Is there a row limit to send Excel files via dbmail?

SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed' Only says "failed" on these items.

Well code is here for people that wants to send excel files by email with header:

EDIT: I did some tests. I created an .xls file with 7k rows and only 1 column. 15kb file and it was sent.

Then I created a file with 26k rows. 1 column. File was sent. 170KB.

And to my surprise, I ran the query, 9krows (bcp crates a 2mb file) and I just copied into an .xls file. The file was 160KB and I could send it via dbmail.

I'm sure it's a size problem and not row limits.

why is bcp creating such big file?

SELECT * FROM msdb.dbo.sysmail_sentitems says the file was sent, but it wasn't.

SELECT * FROM msdb.dbo.sysmail_unsentitems says "retrying". After some minutes there's 0 rows and still I didn't receive the email.

SQL is configured to send 20MB files.

create TABLE ##tempsss
    (
        ..fields VARCHAR(XX) --it needs to be varchar to be able to use header
    )
    
insert into  ##tempsss
SELECT 
...fields...     
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='path\filename.xls'
-------------------------------------------------------------

drop table ##tempsss
-------------------------------------------------------------
6
  • Strange, I don't think so. The query inserts data in a table. Then I queryout the table to the excel file. I can open the file inside the path. I just can't send it via dbmail. – Racer SQL Oct 6 '20 at 18:16
  • Great Idea. I tried that and it failed. at least we know it's not about bad characters. I just want to know if it's about row number or filesize. I don't think is filesize because I have biger files being sent with no problems. – Racer SQL Oct 6 '20 at 18:34
  • Just read a funny story about the english health service losing thousands of contact persons they needed to warn because they had been in contact with a person infected with Covid19. They didn't know Microsoft has a rather low limit on the number of rows in an XLS. Seems an XLSX has a much higher limit. – Gerard H. Pille Oct 6 '20 at 19:33
  • 1
    The 7k is a column limit on older Excel products. As it is, Racer is not generating Excel here but a tab delimited file using unicode characters bcp – billinkc Oct 6 '20 at 21:34
  • Thanks @billinkc, but why it creates the file? I can bcp to the file without error 9k rows. I can open the file and see that theres 9k+ rows in there. I just can't send it via dbmail. I updated the question with this info. top 800 doesnt fail to create the file. It fails to send via dbmail. – Racer SQL Oct 7 '20 at 10:38
0

Database mail has a max file size limit and that might be preventing it. Please check and increase max size in your database mail and I guess it should resolve the issue

1
  • 2
    as in the question I checked this and this is not the problem. also sql server alerts me when this is the problem. – Racer SQL Oct 7 '20 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.