Post reply

Output to a text file

  • February 1, 2006 at 3:06 pm

    #152721

    Hi , I was wondering what would be the best way to output data from my database to a text tile.  Should I use a stored procedure and have the output in XML and then translate to a text file?  any insight would be great.

     

    Thanks

    e...

  • February 1, 2006 at 3:12 pm

    #618232

    Look Up BCP. It will extract data and save to text file.

    Stored procedures cannot easily do this unless you use a sp_cmdshell.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

  • February 1, 2006 at 3:48 pm

    #618240

    thanks a million!

  • February 1, 2006 at 3:56 pm

    #618244

    bcp is one way. I use BCP to export the results from stored procedures.

    You can use SQL Query Analyser .See The best kept secret about SQL Query Analyser. You can run a SQL Script from the windows command prompt by using the OSQL utility.

    I also use sp_makewebtask and sp_runwebtask. With these two you build a simple text template of what you want with place markers for your data.

    You then tell sp_makewebtask to generate a task that applies data to your template to produce an end result file.

    I find this the quickest way of generating a text file (such as an XML file ) that requires specific formatting.

    I have had some success in generating SMTP messages by using the template to generate files into the SMTP Drop folder.

    Finally you can use the Data Transformation Services Wizard to generate a DTS package to export your data to a file.

    LinkedIn Profile

  • February 2, 2006 at 6:23 pm

    #618548

    I agree... BCP is a great tool especially when you learn how to use the format files.  You could also try a simple OSQL call using the SELECT of your choice with either the -o or the ">" character to reroute the output of the SELECT directly to a text file.

     

    --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 3, 2006 at 3:29 am

    #618595

     

    In addition to bcp utility, try the DTS import/export wizard, the source as SQL server and destination as a Text file. You can schedule the export job with.

  • February 14, 2006 at 11:59 am

    #620799

    Thanks everyone, this is great info. 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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