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
-------------------------------------------------------------
2
  • Just a random thought - what if you break the process up into two steps? The first job step creates the extract and ensure it has the full set of expected records. The second, successor step is your send mail call. This approach will allow you to test and determine absolutely - is it an extract issue or a mail issue? Seems like a mail issue but this will narrow the scope of your problem.
    – billinkc
    Oct 7, 2020 at 14:17
  • 1
    Perhaps the mailserver is blocking this? Have you checked those logs?
    – Peter
    Dec 5, 2022 at 7:31

1 Answer 1

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, 2020 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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