Post reply

Send CSV file with db_sendmail - missing records

  • May 19, 2015 at 1:33 am

    #308316

    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 sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv 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

    Does anyone have an idea if something changed in the 2014 edition? Or can give me some tips on what to look at next?

  • May 19, 2015 at 2:50 am

    #1798922

    Found the answer: https://connect.microsoft.com/SQLServer/feedback/details/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

  • May 19, 2015 at 5:26 am

    #1798970

    i had hit this same error a while back, and converted to using a CLR to create all my attachments that i subsequently mail.

    The CLR has access to any tables or temp tables in the context of my connection.

    Also, i had a specific situation where i had to create txt files in ANSI , and sp_send_dbmail creates all files as UTF, so i needed a different, custom way to create txt files. CLR works real well for me for those two situations.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply