Sql-server – Failure sending Excel attachments larger than 1.5MB

database-mailsql-server-2012

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
-------------------------------------------------------------

Best Answer

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