Send CSV file with db_sendmail

Question

I am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv. I have tested this on several servers with 2014 edition on them, and I have the same issue on all of them. Does anyone have an idea if something changed in the 2014 edition? Or can give me some tips on what to look at next?

The query breaks off at different points - for example one of them breaks off at 209 records as i said above, another one at 307. the last record gets truncated. the attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter. what i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:

 select col1, col2, col3 
 from table A1 
 where col1 > 1000 
 order by col1

Answer 1

Found the answer- it's actually a bug on 2014 RTM, please see: https://connect.microsoft.com/SQLServer/feedback/details/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

The bug is fixed in SQL Server 2014 CU4, see KB3004195.

Answer 2

This could be a timeout issue. Try setting the timeout for the account for 5 minutes.

EXEC msdb.dbo.sysmail_update_account_sp 
 @Account_id = <AccountID>, 
 @Timeout= 600, --(Timeout in seconds)
 @No_credential_change = 1

You will have to restart the SQL Server service for this to activate

• olia: Thank you - the timeout is already set to 600 seconds. so it must be something else.