Save SP Results to a TXT File

Question

Post reply

Save SP Results to a TXT File

discreetfunct

Grasshopper

Points: 10
More actions
June 17, 2009 at 9:04 am

#206567

Hi,

I am using SQL 2005.

I have a SP which creates temp tables from a selection of other tables.

At the end of the SP, there is a select statement on a temp table.

I want to save the results of select statement to a TXT file. The catch is this - the TXT file name changes everyday. The name of the TXT file will be the day that it ran on (eg: if it runs on the 01 March 2009 the name of the file must be 01.txt and if it runs on the 02 March 2009 the name of the file must be 02.txt etc..).

I know how to save a file but not with a changing name.

I would like this all done in SQL.

Can somebody assist me with this?

Thanks

Kyle

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

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

J SSCertifiable Points: 7822 More actions · Answer 1

Here is some code that may help. I am using xp_cmdshell to create the file, so you may need to enable it first.

--Enable xp_cmdshell



EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

Now you can create a filename based on the day and create the file.

DECLARE @cmd varchar(1000)



DECLARE @FileName varchar(50)

SELECT @FileName= 'C:\' + RIGHT('0' + CAST(DATEPART(day,GETDATE()) AS varchar(2)),2) + '.txt'

--Export a sample query

SET @cmd = 'bcp "SELECT * FROM sysfiles" queryout "' + @FileName + '" -T -c -t,'

EXEC xp_cmdshell @cmd