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