Incrementing Filename when saved

Filename: 20080806CLM0001.txt

Legend:
YYYYMMDDCLM9999
YYYY = four digit year
MM = two digit month
DD = two digit day
CLM = static for Claim MR
9999 = four digit sequence number (this will keep it unique in case you sent two in a day)

Using master.dbo.xp_cmdshell copy how can I incrementaly save the above filename (20080806CLM0001.txt then 20080806CLM0002.txt then 20080806CLM0003.txt)

Thanks.

re: Incrementing Filename when saved

You have to create a loop that will be used as your sequence. Inside the loop, you do a file check if existing and handle it accordingly. Once the filename you derived (from your sequence) is now unique, you can now save it.

All this would mean the xp_cmdshell is inside your loop.

-- CK