SQL Server Database Mail – How to Send CSV File with db_sendmail

database-mailsql serversql server 2014

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

Best Answer

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.

Related Solutions

Sql-server – way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix

I think you can generate the reports using powershell and then use DBMail to mail the attachments

Write-host "Running SQL Query to export to ANSI CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileAnsi
Write-host "Running SQL Query to export to Unicode CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileUnicode -encoding "unicode"


$dbMailQuery1 = "execute msdb..sp_send_dbmail 
@profile_name = 'msft_profile', 
@recipients = 'seths@microsoft.com', 
@subject = 'Email with Attachment - ANSI attachment', 
@body_format = 'TEXT', 
@body = 'This email has ANSI file attached to it', 
@file_attachments = 'c:\temp\result_ansi.csv' "

Source: http://blogs.msdn.com/b/sqlagent/archive/2011/07/06/email-query-output-as-ansi-unicode-file-attachment-using-powershell-and-database-mail.aspx

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Related Question