Incrementing Filename when saved

[benchpolo]

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_c mdshell copy how can I incrementaly save the above filename (20080806CLM000 1.txt then 20080806CLM0002 .txt then 20080806CLM0003 .txt)

Thanks.

[ck9663]

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