Post reply

Any way to hide column names from result set?

  • February 25, 2009 at 1:01 pm

    #155329

    How do I get rid of the column names on my sql results? I'm trying to out put to a file and this is what I'm getting right now (I'm forcing the commas in between columns):

    PartNumber Description

    -------------------------------------- - ------------------------------------------------------

    widget1 , Normal Size Widget

    widget2 , Large Size Widget

    widget3 , Giant Size Widget

    I want the output as a comma delimeted text file - which would look like:

    widget1, Normal Size Widget

    widget2, Large Size Widget

    widget3, Giant Size Widget

    I've tried a "rtrim" function - but that doesn't seem to help because the column names seem to force the width of my output.

  • February 25, 2009 at 1:09 pm

    #950244

    Tools

    |_ Options

    |_ Query Results

    |_ Results to text

    Uncheck the Include column headers in results

  • February 25, 2009 at 1:12 pm

    #950245

    sorry - I should have said I'm doing this sql statement from an external tool. Is there any way to pass something in the sql statement to do this?

  • February 25, 2009 at 1:13 pm

    #950246

    I would guess that depends on the tool.

  • February 25, 2009 at 1:18 pm

    #950247

    I'd say use a decent text editor and delete the extra tabs.

  • February 25, 2009 at 3:04 pm

    #950309

    I think this will get close to what you want:

    SELECT PartNumber + ',' + Description

    FROM table

    It should return:

    ------------------------------------

    widget1, Normal Size Widget

    widget2, Large Size Widget

    widget3, Giant Size Widget

  • February 25, 2009 at 4:53 pm

    #950340

    e wants to hide the column names in his presentation layer; he's going to get column names regardless, right?

    also i think his columns are char and not varchar, so he might still get the wide results,

    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!

  • February 25, 2009 at 5:37 pm

    #950347

    The datatype is nvarchar(20) for the partnumber and nvarchar(100) for the description - so this works perfectly: SELECT PartNumber + ',' + Description EXCEPT that it has a gazillion of these: ---------------- at the top which seems to impact the output (lines look just as long - but data is squeezed up front)

    One last thing.....does anyone know how to get rid of this at the end?:

    -------------------

    (33 rows affected)

  • February 25, 2009 at 6:22 pm

    #950353

    SET NOCOUNT OFF before your SELECT will get rid of the rows affected. Not sure about the dashes, somebody else posted about that here recently, don't know if there was a good solution

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • February 25, 2009 at 9:43 pm

    #950404

    jcrawf02 (2/25/2009)

    SET NOCOUNT OFF before your SELECT will get rid of the rows affected. Not sure about the dashes, somebody else posted about that here recently, don't know if there was a good solution

    Actually, IIRC, the would be SET NOCOUNT ON before the query and SET NOCOUNT OFF after the query.

  • February 25, 2009 at 11:07 pm

    #950428

    n_parker (2/25/2009)

    The datatype is nvarchar(20) for the partnumber and nvarchar(100) for the description - so this works perfectly: SELECT PartNumber + ',' + Description EXCEPT that it has a gazillion of these: ---------------- at the top which seems to impact the output (lines look just as long - but data is squeezed up front)

    One last thing.....does anyone know how to get rid of this at the end?:

    -------------------

    (33 rows affected)

    Does your app need it as a file by any chance? BCP will do it without a header... so will OSQL if you use the -h-1 switch.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 26, 2009 at 5:12 am

    #950515

    Lynn Pettis (2/25/2009)

    jcrawf02 (2/25/2009)

    SET NOCOUNT OFF before your SELECT will get rid of the rows affected. Not sure about the dashes, somebody else posted about that here recently, don't know if there was a good solution

    Actually, IIRC, the would be SET NOCOUNT ON before the query and SET NOCOUNT OFF after the query.

    ON and OFF are synonyms, aren't they? 😛

    Thanks, sorry 'bout that.

    Jon

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • January 28, 2011 at 2:46 am

    #1279585

    Has anyone post a solution to this?

    I have the same issue that when I execute a stored procedure, thro a sql job via sql agent, I get the output to a .txt file.

    The result output shows the column names and also the ------- dashes in the top of the file and then the actual data.

    Is there anyway, I can send the data output only (excluding the column name and the dashes -----)

    Thanks for anyone who has an idea.

  • January 29, 2011 at 9:11 am

    #1280086

    Lynn Pettis (2/25/2009)

    jcrawf02 (2/25/2009)

    SET NOCOUNT OFF before your SELECT will get rid of the rows affected. Not sure about the dashes, somebody else posted about that here recently, don't know if there was a good solution

    Actually, IIRC, the would be SET NOCOUNT ON before the query and SET NOCOUNT OFF after the query.

    Actually, you don't need the SET NOCOUNT OFF. It's NOCOUNT ON has procedure sensitive scope.

    Heh... and, yeah... I realize I'm responding to a 2 year old post. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 29, 2011 at 9:13 am

    #1280087

    puja63 (1/28/2011)

    Has anyone post a solution to this?

    I have the same issue that when I execute a stored procedure, thro a sql job via sql agent, I get the output to a .txt file.

    The result output shows the column names and also the ------- dashes in the top of the file and then the actual data.

    Is there anyway, I can send the data output only (excluding the column name and the dashes -----)

    Thanks for anyone who has an idea.

    I'm not sure what type of privs you have, but BCP using QUERYOUT will do exactly what you wish.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

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