January 27, 2016 at 4:21 pm
Hi guys, i have problem to send a big file with sp_send_dbmail in SQL SERVER 2008 R2 SP2 Enterprise Edition, for example , i have a query result with 8000 rows and i need to send this result in a CSV File attachment
i'm using this code
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients='email@xxxx.com',
@subject='CSV Extract',
@body='See attachment',
@query='
SET NOCOUNT ON;
select ''sep=;''
select ''X'',''Y'',''Z'',''A'',''B'',''C'',''D'',''E'',''F'','' G''
EXEC DataBase.[dbo].[IT_sp_XXXXX]
SET NOCOUNT OFF;
',
@query_attachment_filename = 'NameFile.csv',
@query_result_separator = ';',
@attach_query_result_as_file = 1,
@query_result_no_padding= 1,
@exclude_query_output =1,
@append_query_error = 0,
@query_result_header =0;
i don't have problem if the result is less 4000 rows , but when the result is more than 4000 the email never arrieved ...
any suggest ?
January 27, 2016 at 5:19 pm
See Sammy Larbi's post: http://stackoverflow.com/questions/997628/how-do-i-increase-the-maximum-allowed-attachment-size-for-email-sent-using-msdb
In the Object Explorer frame of SQL Server Management Studio, expand the "Management" folder. Open the Database Mail configuration wizard.
Click Next-> Choose the radio button for "View of change system parameters" and click Next.
Now change the Maximum File Size (Bytes) property and click Next, then click Finish to save your changes.
See Database Mail Configuration Wizard at MSDN for more info.
-Regards,
January 27, 2016 at 7:40 pm
Since you said you can send your results if you're sending less than 4000 rows, it sounds like you have database mail set up correctly. I don't think there's any configuration for the maximum email size for database mail. Have you checked the limitations on the SMTP server you're using to send the email? You may have a limitation there you need to worry about.
Also, have you checked the status of your email in MSDB? You should be able to see the rows in msdb.dbo.sysmail_mailitems. If you have any errors, they'll be in msdb.dbo.sysmail_log. You could have multiple errors in sysmail_log for a single mailitem_id in sysmail_mailitems. If you can get the actual error message frmo sysmail_log, that will go a long way in determining what came back from the SMTP server. Post back with what you find and let's see where it goes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply