I am running a SP that entries that are older than 6 months. These entries have a file associated with them. Is there anyway I can delete that physical file with SQL server too? Its a Win2K3 server with sql server 2000.
12:58 pm on Apr 20, 2010 (gmt 0)
I doubt that the SQL server can directly delete files. Odds are that whatever permissions it is running as only gives it access to it's own data files.
4:07 pm on Apr 20, 2010 (gmt 0)
It's clunky, but in theory you COULD write a DTS package that runs a batch file and execute that DTS package from within the stored procedure. As far as the batch file, you should be able to dynamically create that from within the SP.
Don't ask me for the specifics- I've never done anything like that. :) For something else I'm working on, I need to execute a DTS package through a stored procedure. But it's a low priority and I haven't gotten very far with it. (But what I have found so far is that you need to create the DTS procedure, schedule a job for it, then access it through the job queue to execute it through a SP. Like I said, I haven't gotten very far with the specifics yet...)
Instead of trying to do everything within MS SQL, a better way might be to use PHP, ASP, Cold Fusion, or some other language that has more robust file system tools to write a routine to call the SP and delete the files.
4:17 pm on Apr 20, 2010 (gmt 0)
Yes VB would be the best bet, I just didn't develop the application and I didn't want to have to edit more than one aspect of the system. I will just remove the files with VB. This is a dotnetnuke site. Thanks,