Output of stored procedure to a txt file??

  • shasag1

    SSC Veteran

    Points: 278

    Hello,

    I am trying to write the output of a stored procedure to a txt file. I am unable to find any technical matter on the same.

    can anyone help me in this regard.

    I want to call that output txt file from a ASP page later.

    Thanks

  • Andy Warren

    SSC Guru

    Points: 119675

    One kinda cool way to do is by using the web export wizard.

    Andy

  • wolfjr

    SSChasing Mays

    Points: 603

    Thers one article from Brian Knight, that explain how to do that using Query Analyzer.

    http://www.sqlservercentral.com/columnists/bknight/tippipequery.asp.

    WolfJr.

  • Leon Platt

    SSCommitted

    Points: 1921

    You may want to play with calling the sp using isql or osql (see bellow pulled from T-SQL help file):

    Using the osql Utility (isql could be used too)

    The osql utility is a Microsoft® Win32® command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts. To use osql, users must understand Transact-SQL.

    The osql utility is typically used in these ways:

    Users interactively enter Transact-SQL statements in a manner similar to working on the command prompt. The results are displayed in the command prompt window.

    Users submit an osql job either specifying a single Transact-SQL statement to execute or pointing the utility to a text file that contains Transact-SQL statements to execute. The output is usually directed to a text file, but it also can be displayed in the command prompt window.

    Other options are that you could build an Active X control that you could instantiate in the sp and feed it the output parameters having the Active X control assemble the resulting text file and output it using the file system object.

  • Jags2001

    SSCommitted

    Points: 1529

    To output the results of a script.

    osql -E -d%database% -Q"exec sp_..." -n -ooutput.txt

    You can use the -i flag to execute a script

    and -o or the >

    >> appends the output to the file.

  • Justin-14543

    SSCrazy

    Points: 2017

    Can't you try using xp_cmdshell?

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • mmrajesh

    SSC Journeyman

    Points: 84

    wolfjr - Tuesday, August 21, 2001 6:20 AM

    Thers one article from Brian Knight, that explain how to do that using Query Analyzer. http://www.sqlservercentral.com/columnists/bknight/tippipequery.asp. WolfJr.

    sorry - we were looking from Stored procedure - not from direct select command.

  • pietlinden

    SSC Guru

    Points: 62395

    Umm... that post you're adding to is SEVENTEEN YEARS old.
    How about...
    https://sqlwithmanoj.com/tag/bcp-queryout/

  • Jeff Moden

    SSC Guru

    Points: 994284

    pietlinden - Thursday, January 31, 2019 6:12 PM

    Umm... that post you're adding to is SEVENTEEN YEARS old.
    How about...
    https://sqlwithmanoj.com/tag/bcp-queryout/

    Heh... ya gotta admit, though... it's still a frequent question. 😀

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

Viewing 9 posts - 1 through 9 (of 9 total)

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