Output to File? 2

[BobLoblaws]

I am running SQL Server 7.0. I am looking for a way to output a query to a file. The filename needs to be dynamic. The logic should work like follows.

Stored procedure is passed a date stored in @inDate.

SELECT * INTO FILE "c:\" + @inDate + ".txt" FROM tblHistory WHERE Date= @inDate.

I would like it to dump all information on that date into a file named the date. ie. 2003-02-12.txt

Any suggestions?
Thanks,

 

[gerson37]

I would suggest using DTS to output and name the file.

 

[JamesLean]

You'll need to use bcp to do the exporting and build the statement dynamically within your SP. It might look something like this:

CREATE PROC apExportHistory
  @inDate datetime
AS

DECLARE @filename varchar(50)
DECLARE @cmd varchar(200)

SET @filename = 'c:\' + CONVERT(varchar(8), @inDate, 112) + '.txt'

SET @cmd = 'bcp "SELECT * FROM tblhistory WHERE [date] = ''' + CONVERT(varchar(25), @inDate) + '''" queryout ' + @filename + ' -c -Sserver -T'

EXEC master..xp_cmdshell @cmd
GO

The first CONVERT (when constructing the file name) uses style 112 to get date in format yyyymmdd. Look up CONVERT in BOL for more info on this.

I've not tested this but hopefully it should be somewhere near what you need. --James

 

[BobLoblaws]

Thanks James! That is exactly what I need.