I have two folders that the database writes files to using the directory access features. The destination folder is a landing point for documents requested by a webpage. Once they are viewed by the webpage they are no longer useful and can be cleaned up at some point.
My initial plan was to have an event that ran each night to delete files from this folder that had a create date older than 30 minutes prior.
This query displays the records I would want to delete correctly:
SELECT * FROM ClientDataLand WHERE create_date_time <= DATEADD(MINUTE,-30,NOW())
The problem is that generates Error code -728 -- Update operation attempted on non-updatable remote query.
So how could one delete from a directory access server based on conditions, such as the create time?
asked 11 Sep '12, 13:21
Directory Access Servers are pretty picky about the type of cursors allowed on them. You can get around the issue by using a cursor in a stored procedure that only calls fetch next :
create procedure DelFiles ( in @expire integer ) begin declare @fn varchar(128); declare @cur cursor for select file_name from DBA.MyDir where create_date_time < dateadd( minute, -1 * @expire, now() ); open @cur; fetch next @cur into @fn; while sqlcode = 0 loop delete from DBA.MyDir where file_name = @fn; fetch next @cur into @fn; end loop; close @cur; end
In this example, I've allowed you to pass in a parameter specifying how many minutes old a file should be to be deleted.
Just to add: Nick Elson has answered a similar question in the general NG (thread "proxy table usable columns for delete" from 2012-08-09) - explaining the cause:
Obviously, the suggested solution is exactly what Reg has implemented:)