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

• Michael J Waibel

  SSC Rookie

  Points: 47

  More actions

  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

   

• Lowell

  SSC Guru

  Points: 323522

  More actions

  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!

• Michael J Waibel

  SSC Rookie

  Points: 47

  More actions

  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?

• Michael J Waibel

  SSC Rookie

  Points: 47

  More actions

  February 16, 2007 at 2:59 pm

  #690341

  I do not have the stored procedure xp_cmdshell.

  What now??