Post reply

create a stored procedure to create a txt file

  • April 21, 2010 at 3:37 am

    #220780

    Hi,

    I need to create a stored procedure that accepts a parameter, this paramentes is a name. When i pass the name to the stored procedure, this procedure creates a empty txt file with the name that i passed as the parameter.

    Is it possible to do this using t-sql?

    Thank you

  • July 9, 2010 at 4:12 am

    #1190688

    I can't believe this is the slickest way to achieve what you're after, but this works :w00t:

    [font="Arial Black"]CREATE PROCEDURE EXPORT_DATA

    @FileName varchar(255)

    as

    begin

    declare @bcpCommand varchar(255), @Result int

    set @bcpCommand = 'bcp "Select 1 as a" queryout "' + @FileName + '" -c -t, -T -S '

    exec @Result = master..xp_cmdshell @bcpCommand, no_output

    end[/font]

    Don't forget that xp_cmdshell is not enabled in the db configuration by default

    Mike.

  • October 7, 2010 at 3:52 am

    #1233368

    Is there any other way to create store procedure

  • October 8, 2010 at 8:57 am

    #1234325

    You can use sp_OACreate to access the the File System Object (Active X)

    reading & writing files in sql server[/url]

    Director of Transmogrification Services

  • March 2, 2011 at 9:39 am

    #1293592

    Hi,

    Here the txt file will be created in server. Is it possible to create a file in local machine?

  • March 2, 2011 at 10:17 am

    #1293612

    The file will be created from the context of the sql server, so you should be able to write it to any drive or share the sql server has access to.

  • March 2, 2011 at 10:31 am

    #1293621

    A little more information would be nice as to what the big picture of this is... Is the parameter some data that needs to be retrieved from SQL? I could whip out a VBS script to hit the DB, grab the value, and pump it into the script. Powershell possibly?

    Director of Transmogrification Services

  • March 3, 2011 at 6:05 am

    #1293951

    Why not use an SSIS package to generate the file?

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

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