By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,637 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,637 IT Pros & Developers. It's quick & easy.

Delete a file from the Server

P: n/a
I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):

xp_cmdshell "delete <filename>"

If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.

From,

Ryan
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 28 Jul 2003 11:12:32 -0700 in comp.databases.ms-sqlserver,
rv*******@aol.com (Ryan) wrote:
I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):

xp_cmdshell "delete <filename>"

If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.


(untried)
xp_cmdshell "cmd /c del <filename>"

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)
Jul 20 '05 #2

P: n/a
"Ryan" <rv*******@aol.com> wrote in message
news:3d**************************@posting.google.c om...
I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):

xp_cmdshell "delete <filename>"

If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.

From,

Ryan


You can use xp_cmdshell in a trigger, but this may not be the best approach,
especially if there are very frequent deletes. Calling xp_cmdshell is a
relatively slow operation, and everything will be blocked while the trigger
runs. Also, if there are any problems executing xp_cmdshell, such as a
timeout on a network drive, or a slow file delete, then there may be a
significant impact on your main table. Finally, if someone deletes 20 rows
in a single delete statement, you will have to iterate through them with a
loop or cursor in your trigger, calling xp_cmdshell for each file name, and
that could really cause performance problems.

A more flexible approach might be to use the trigger to put the file name(s)
into a 'pending deletion' table, then have a SQL Server job which checks the
table every few minutes and deletes the file. Using xp_cmdshell for that
would be fine, because you don't care (as much) if it times out. Or even
have an external script read the table and do the deletion from the
operating system side. By doing it that way, any unexpected behaviour won't
impact your main processing.

Simon
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.