Post reply

Saving Query Results to .txt using Scripts

  • September 15, 2009 at 7:07 am

    #135017

    Hi All,

    I need to save the results of any Query to a delimited .txt (Any Delimiter)

    however i need it to be saved from within a SQL Proc, which will be called every night

    in a job.

    Any Help Please...

  • September 15, 2009 at 7:17 am

    #1053745

    Hi,

    You can use bcp with xp_cmdshell or openrowset. Please refer to the following links. I recommend you to use open rowset. Please refer the following links.

    bcp with xp_cmdshell

    http://www.mssqltips.com/tip.asp?tip=1633

    openrowset

    http://www.mssqltips.com/tip.asp?tip=1202

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • September 15, 2009 at 7:20 am

    #1053748

    both bcp and the more robust sqlcmd have the ability to be fed a query and output the results to a file;

    that is your best choices, unless you write/find a CLR to do it for you instead.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 15, 2009 at 8:08 am

    #1053793

    Thanks Guys,

    Im looking into it now...

  • September 15, 2009 at 8:11 am

    #1053797

    If you are using a job to send the results to a .txt file, the one alternative way would be to create a SSIS package to extract the data and create the file and then schedule this package as run as your nightly job.,

  • September 15, 2009 at 11:08 am

    #1053973

    Hi,

    i faced a similar problem and i created a stored procedure that uses bcp, fmt format file and xp_cmdshell..

    The code follow this:

    select @cmd = 'bcp "SELECT column from table" queryout ' D:\exported.txt -f D:\format.fmt - T

    EXEC @rc = master..xp_cmdshell @cmd

    IF @rc 0

    BEGIN

    PRINT 'cmd faillure!'

    return 1

    END

    ELSE

    BEGIN

    Print 'cmd execution ok!'

    return 0

    END

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

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