BCP out and headers

I'm no dummy when it comes to this stuff but for the life of me I can't figure out how (if it is possible) to get column headers to go when exporting data. Here is the syntax I'm using:

BCP "select ATSCLAIMNUMBER, ALTERNATECLAIMNUMBER, LNAME , FNAME, MNAME, convert(varchar(10),LOSSDATE,101), convert(varchar(10),ERNOTIFIEDDATE,101), convert(varchar(10),CLOSINGDATE,101), STATUSCODE, INDPAID,MEDPAID,REHABPAID,EXPPAID,LEGALPAID,TOTALP AID,INDFUTURERES,MEDFUTURERES,REHABFUTURERES,EXPFU TURERES,LEGALFUTURERES,TOTALFUTURERES,EXCESSRECOVE RY1,EXCESSRECOVERY2,EXCESSRECOVERY3,EXCESSRECOVERY 4,EXCESSRECOVERY5,EXCESSRECOVERY,OTHERRECOVERY1,OT HERRECOVERY2,OTHERRECOVERY3,OTHERRECOVERY4,OTHERRE COVERY5,OTHERRECOVERY FROM ##MMAACTUARY" queryout C:\mma03182004.TXT /c /t, /r \n /U demo /P demo /S ATSDEV\ATS2K

It spits out the file fine. This is all built with dynamic SQL and needs to email the file which is does perfectly. My only problem is that there are no headers which I can't have. Any suggestions?How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.|||I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott|||It's not what I imagined but it does look like it would work. I really don't care at this point how it happens so long as it does! I'll try this and report back with the results.|||Originally posted by hmscott
I think I remember a post like this earlier. As I recall, Brett Kaiser recommended a strategy whereby the SELECT statement was UNIONed to another select statement with the column names as the result set. The problem with this strategy (as I recall) was that all the columns had to be typed as varchar. So something like:

SELECT
'MyColumn' as Column1,
'TwoColumn' as Column2

UNION

SELECT
Cast(ATSCLAIMNUMBER as varchar(20)),
Cast(ALTERNATECLAIMNUMBER as varchar(20))
FROM
##MMAACTUARY

Not elegant, but I think it worked.

HTH,

hmscott

Hey, thnaks fo rremebering...and you know what?

bcp -c

and, what's not elegant about it?

I use Union ALL btw

AND to make sure the rows come out correctly...

For fixed width:

SELECT Col1,Col2,Col3 FROM (
SELECT 'Heading1' AS Col1
, 'Heading2' AS Col2
, 'Heading3' AS Col3
, 1 AS RowOrder
UNION ALL
SELECT Col1
, CONVERT(varchar(25), myDatetimeCol99)
, CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

For Delimeted:

SELECT datarow FROM (
SELECT 'Heading1,Heading2,Heading3' AS Datarow
, 1 AS Roworder
UNION ALL
SELECT Col1+','
+','CONVERT(varchar(25), myDatetimeCol99)
+','CONVERT(varchar(25), myIntCol99)
, 2 AS RowOrder
FROM myTable99
) AS XXX
Order by RowOrder

And usually with delimeted, I wrap all the data in quotes

''''+ Col1 + ''''
','+ ''''+ Col2 + ''''|||Hey, and dig it, you can even add a trailer with audit counts...

Just make it datarow 3 and do a select count(*)...you could even sum amount if you want to go crazy...

AND you could add a row "type" to each one, to make it easier for extraction of the non data row

WHERE SUBSTRING(datarow,1,1) IN ('H','T')|||Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott|||Originally posted by hmscott
Brett,

My humblest apologies for suggesting that your solution was "not elegant". :-}

Actually, I need to spend some more time learning BCP and get off of my DTS crutch.

BTW, I changed jobs and have been thrown into what seems to be a real lion's den.

Ugh.

Regards,

hmscott

IMNSHO, I wouldn't be to quick to give up DTS. I don't think there is much you can do with BPC that you can't do with DTS. There is a bunch you can do with DTS that you can't do with BCP. There isn't an "ELEGANT" solution here just not as ugly.|||It does work but the string got too long. So, what si did was create a seondary table and inserted the header values. This table only has one row so when using that select statement unioned with the real select statement it works perfectly. If any one wants a copy of the procedure email me jfogel3@.msn.com|||Originally posted by hmscott
BTW, I changed jobs and have been thrown into what seems to be a real lion's den.


Sorry to hear that...

I avoid DTS for any production related issues...seen to many thing I couldn't explain...

Mostly with connections and changing them...Seems like it's lookin at an earlier version sometimes...no thanks...

And I love Nigels sig...

Cursors are useful if you don't know SQL
DTS can be used in a similar manner
Beer is not cold and fizzy

Or a paraphrase...since it seems like sqlteam is down...again....

Oh, and no apologies please....

(otherwise I'll have to do it all the time...)|||ooopps...here's the thread

They're back up...

and it's...

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.