Extra line between Header and data in SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extra line between Header and data in SQL query

Author

Topic

atuljadhavnetafim
Starting Member

25 Posts

Posted - 2015-03-24 : 05:15:21

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!

Atul Jadhav

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-24 : 08:18:27

this is really cheesy, but you could do something like this using the no header output if you want to use do all the work via send mail.

CREATE TABLE #Sample
(
ID INT IDENTITY(1,1),
SomeDate Datetime,
SomeString Varchar(30)
)

INSERT INTO #Sample
VALUES('1/1/2014','Blah'),('1/1/2015','Bleh')

;With MyOutput
as
(
SELECT 'ID' ID,'SomeDate' SomeDate,'SomeString' SomeString, 1 OrderBy
UNION
SELECT CAST(ID as varchar(10)),CONVERT( varchar,SomeDate, 101), Somestring, 2
FROM #Sample
)

SELECT ID,SomeDate,SomeString FROM MyOutput
ORDER BY OrderBy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources