Genrate .txt file through SQL server 2005 stored procedure

  • Hi,

    I want to genrate .txt file through stored procedure in SQL server 2005.

    Is anybody have idea how to create txt file through SQL query??

  • You could use xp_cmdshell:

    exec master..xp_cmdShell 'echo my file > c:\text.txt'

    However you might have to change your security configuration to get this to work.

    This link has some more examples:

    http://www.nigelrivett.net/SQLTsql/WriteTextFile.html

    John

  • If you want to create a text file with procedure output then you can use SSIS or BCP or SQLCMD or osql too...

    bcp "exec procedurename" queryout "file.txt" -T -c

    MohammedU
    Microsoft SQL Server MVP

  • Any specific reason why it HAS to happen in a stored proc?

    Why cant you pipe out the results from the stored proc with the use of SSIS in BIDS?

  • how to create 10 files using xp_cmdshell?

    Thanks.

  • pduplessis-723389 (6/2/2008)


    Any specific reason why it HAS to happen in a stored proc?

    Ummm... because it's damned convenient? 🙂

    --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)

  • Sourav-657741 (2/10/2011)


    how to create 10 files using xp_cmdshell?

    "It depends" on where the data for the files is coming from. Personally, I create a .bat file and call that from xp_cmdshell.

    You can get away from using xp_cmdshell using a bit of OLE Automation. Consider the following and understand with CMD /C actually does.

    DECLARE @ShellRefNum INT

    EXEC sp_oaCreate 'wScript.Shell', @ShellRefNum OUT

    EXEC sp_oaMethod @ShellRefNum, 'Run', NULL, 'CMD /c "DIR C:\ /s /a-d /b" > c:\Temp\WScriptTest.txt'

    EXEC sp_oaDestroy @ShellRefNum

    --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 7 posts - 1 through 6 (of 6 total)

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