Post reply

SQL Server 2000, Query Results To Text File question??

  • February 16, 2007 at 2:14 pm

    #176778

    I want to have a SQL query autimatically send it's results to a text file whose name will change based on the current date.

    I a looking for commands and proper syntax to do this.

    If I can't do this within the query itself then I ned to know how to do it in a stored procedure or in

    other pieces/steps.

    Please help!! I am very green when it comes to query commands and syntax.

    Thanks,

     Mike

     

  • February 16, 2007 at 2:35 pm

    #690333

    here's an example i had handy: filename is build with a combination of variables and current time/date.

    replace your query  where the query in red is below.

    declare @outfile    varchar(250),

            @strCommand varchar(1000)

    --ie filename for server "daisy" 

    --daisy_Query_Results-20070216_16-28-08-447

    SELECT @outfile=LOWER(@@SERVERNAME)+'_Query_Results-' + convert(varchar,getdate(),112) + '_' + replace(convert(varchar,getdate(),114),':','-')

    set @outfile = 'C:\' + @outfile

    SET @strCommand = 'bcp "select top 5 * from sysobjects where xtype=''U''"'

           + ' QUERYOUT "' + @outfile + '" -T -S' + LOWER(@@SERVERNAME) + ' -c'

     PRINT 'EXEC master..xp_cmdshell ''' + @strCommand + ''''

     EXEC master..xp_cmdshell @strCommand, NO_OUTPUT

    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!

  • February 16, 2007 at 2:41 pm

    #690334

    Thanks,

    Can this be done in Query Analyzer or does have to be done in a stored procedure?

  • February 16, 2007 at 2:59 pm

    #690341

    I do not have the stored procedure xp_cmdshell.

    What now??

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

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