Query Output in a text file

  • Hi,

    My requirement is to run a query and the o/p of the query has to go into a text file (new file to be created). Do we have any workarounds for acheiving this?

    Thanks in Advance!

  • Maybe I am misunderstanding your question, but to get the results from a query into a text file either execute the query by CTRL+SHIFT+F or change the setting via Query | Results To | Results To File. Either way, when executed will then display a request for where to save the output and what format you wish to use.

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

  • What kind of text file? If it's a CSV or other delimited type of file you can do it with bcp very easily. Look it up in BOL.

    To be able to call bcp (a command-line function), you'll need to

    1. Set the configuration option 'show advanced options' to 1. Issue a RECONFIGURE command

    2. Set the configuration option 'xp_cmdshell' to 1. Issue another RECONFIGURE command

    There's a slight security exposure while these two are on, so be sure to set them back to zero (0), in reverse order, when you're done with the file upload.

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • Are you trying to create a new text file and write to it within SQL query only?

    Following would be a helpful snippet for how you may achieve that.

    DECLARE @Text AS VARCHAR(100)

    DECLARE @Cmd AS VARCHAR(100)

    SET @Text = 'Hello world^| '

    SET @Cmd ='echo ' + @Text + ' > C:\AppTextFile.txt'

    EXECUTE Master.dbo.xp_CmdShell @Cmd

    Although this is really not a good way to write data to a text file: usually SQL Server should not have permission to write to the root of the C: drive, and xp_cmdshell is disabled by default.

  • Although I think this post definitely deserves an answer, be aware that the OP might not respond because it is a 3 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)

Viewing 5 posts - 1 through 4 (of 4 total)

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