I am running the following script every 2 hours, what I want is to only keep 5 current, but each to have time stamp on the file. ie myview.xml will be myview010.xml for file generated at 10.00am myview.xml for file generated at 12.00
the current script I am using setup in an sql agent job to run every two hours is
exec master..xp_cmdshell 'bcp "SELECT * FROM mydatabase..myview for XML RAW(''myview''), XMLSCHEMA(''URN:myview.COM'')" queryout \\webserver\internet\myview.xml -S MYserver -U testuser -P xxxx -c -t'
how do i insert a timestamp in the file name?
or we could have myview1.xml, myview2.xml, myview3.xml?
After 5 files have been created I want to remove the earliest
can sql do this or would it better that windows schedular remove the files on the webserver?