Post reply

Genrate .txt file through SQL server 2005 stored procedure

  • May 29, 2008 at 4:19 am

    #103499

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

  • May 30, 2008 at 9:43 am

    #822426

    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

  • May 30, 2008 at 3:54 pm

    #822577

    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

  • June 2, 2008 at 10:14 am

    #823049

    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?

  • February 10, 2011 at 1:17 pm

    #1285445

    how to create 10 files using xp_cmdshell?

    Thanks.

  • February 13, 2011 at 11:30 am

    #1286173

    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)
    Intro to Tally Tables and Functions

  • February 13, 2011 at 11:33 am

    #1286174

    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)
    Intro to Tally Tables and Functions

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

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