Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    How about using osql with a "-s," parameter? Might want to add a -n -w 5000 also.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: BCP out and headers

    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:

    Code:
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: BCP out and headers

    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:

    Code:
    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 + ''''
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245

    Re: BCP out and headers

    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

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Re: BCP out and headers

    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.
    Last edited by Paul Young; 03-19-04 at 11:58.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: BCP out and headers

    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....)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ooopps...here's the thread

    They're back up....

    and it's...

    Code:
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •