sp_send_dbmail dashes separator line

Question

I need to have an automated process to generate and send out csv data on a set schedule. Using the sp_send_dbmail routine is working great except for one small problem I am having trouble getting around.

I am specifying a query to be run and including the results as a file attachment. The customer wants a csv file with column names.

The attachment always includes a 'separator line' of dashes between the column header and the actual data. Is there any way to eliminate this line of dashes? When I use @query_result_header = 0 flag, it removes the line of dashes, but the column names are also removed as well.

I want to keep the column names, but just remove the line of dashes. Any ideas?

Thanks!

Answer 1

Hi Coulson,

Frankly speaking I din't think so its possible, yes its possible to remove headers which automatically remove these lines.

---

Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)

Answer 2

In case anyone else encounters this problem, I was able to come up with a workaround.

To eliminate the line of dashes that separate the header from the data, you can use a Union statement as a workaround.

The first select is just a listing of the column names. Union this to a second select that is the actual query.

Now you can set the @query_result_header flag to 0 because you have the column names in the actual result set.

There may be some additional steps required such as converting all results from the query to strings or using additional fields for sorting to ensure that the column names are always returned first.

Answer 3

Hi Coulson,

Gr8 finding... Its good..

---

Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ Please mark "Propose As Answer" if my answer helped..... :)

Answer 4

How did you go about getting the headers to the top though when you UNION the two SELECT queries.

 

My SELECT which selects just column headers is always returned to the bottom, even by converting all fields to string how would I then get the columns to the top? Unless I add a '1' field at the end but that would screw with the data.

 

Cheers,

Answer 5

Have a seperate column SortOrder for each row. The titles would be 0, and any other value for the other rows. When you do a select from the the inner select, do not use the sort order.

IE

    SELECT val, key

    FROM (

                SELECT 0 as SO, 'Val' as val, 'Key' as key

                UNION ALL

                SELECT 1, Val, Key

                FROM SomeTable

    ) AS InnerTable

    ORDER BY SO