File Rotation

Hi,
I currently have a job setup to export a view from a MS
SQL 2000 database to a CSV file on a network drive..
Let's say the filename is: filename.csv
The job runs every 10minutes, and I need it to save to
different file names for 7 days, then start back..
day 1: filename1.csv ..10minutes later would be
filename2.csv etc...
After 7 days it would go back to filename1.csv..
I hope I'm being clear here but I'm sure someone gets the
idea.. anyone has a tip?Guillaume
How about using the day of the week as part of the
filename and overwriting if the file name exists. ie
filename-mon.csv, filename-tue.csv etc or you could just
set up a small (one row, one column) table on your
database where you keep a number or some other identifier
to append to the file name. If you start with '1' as your
initial value, add 1 each day and when it gets to 8 reset
to 1. Filename1.csv, filename2.csv etc
Hope this helps
Regards
John

file handling

Is there function in SQL to return just the filename. For eg.
if the filename is 'C:\test\job.txt' , i want to extract job from the
filename. Is is possible? Thanks in advance!There's nothing built-in but you can accomplish the desired result with some
Transact-SQL string functions:
DECLARE @.FilePath varchar(255)
SET @.FilePath = 'C:\test\job.txt'
SELECT REVERSE(LEFT(REVERSE(@.FilePath), CHARINDEX('',
REVERSE(@.FilePath))-1))
Hope this helps.
Dan Guzman
SQL Server MVP
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||HP,
If the path is stored in a column, the T-SQL string functions (i.e.,
CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
there are a few undocumented extended stored procedures that can be used to
work with files using T-SQL.
HTH
Jerry
"HP" <HP@.discussions.microsoft.com> wrote in message
news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
> Is there function in SQL to return just the filename. For eg.
> if the filename is 'C:\test\job.txt' , i want to extract job from the
> filename. Is is possible? Thanks in advance!|||Thanks!
"Jerry Spivey" wrote:

> HP,
> If the path is stored in a column, the T-SQL string functions (i.e.,
> CHARINDEX, SUBSTRING, etc...) can be used to return just the name. Also,
> there are a few undocumented extended stored procedures that can be used t
o
> work with files using T-SQL.
> HTH
> Jerry
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:9368C79B-35DF-4D68-9A56-E97613D74416@.microsoft.com...
>
>