help me deleting bulk records and images at once??

[zathrone]

SQL server 2000 and asp
------------------------
I have a typical problem, plzz give a look at the attachment it is well described over there.

I want to delete image related to the record b4 deleting the records. And i have to delete any numbers of records at once... can be 5000 to 10,000 at once.
The images are located in the disk not in database like c:\images..

can anyone help me write a procedure for it?

Thanking you in advance

[skhanal]

You can delete files using xp_cmdshell command within the same procedure where you delete records.

[zathrone]

You can delete files using xp_cmdshell command within the same procedure where you delete records.

It is possible but I read somewhere with xp_cmdshell it is said that only administrative rights can access that command or permission issue is tough to maintain for that command to work for every users.
Is it possible to make that command available public?

--is there any other ways except it?

[rmiao]

It's possible but have to think about security impact.

[mikr0s]

a FileSystem Object could be used
(this could also be done in your asp pages, instead of having the "database server" doing the job)
Note:Only members of the sysadmin fixed server role can execute sp_OACreate. (sp_OACreate is used for creating the FSO)

--declare table variable holding names of images that will be deleted
DECLARE @images TABLE (ident INT IDENTITY(1, 1),
image_name NVARCHAR(100))

--variables for implementing a "virtual" cursor
DECLARE @runner INT, @limit INT, @file_name nvarchar(100)

--varialbes for FSO handling
DECLARE @object INT, @error INT, @exists INT
DECLARE @src VARCHAR(255), @desc VARCHAR(255)
DECLARE @command VARCHAR(120)

--get images that are going to be deleted
INSERT INTO @images(image_name)
SELECT ImageName
FROM tblImage
WHERE ....deletion criteria go here...

--create FileSystem Object
EXEC @error = sp_OACreate 'Scripting.FileSystemObject', @object OUT

IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc
RETURN
END

--run through images
SELECT @runner = 1, @limit = ISNULL(MAX(ident), 0)
FROM @images

WHILE @runner <= @limit
BEGIN

--get current image name
SELECT @file_name = image_name
FROM @images
WHERE ident = @runner

--check if file exists using filesystem object, (procedure master..fileexist could also be used)
--EXEC master.dbo.xp_fileexist @file_name , @exists OUTPUT

EXEC @error = sp_OAMethod @object, 'FileExists', @exists OUT, @filespec = @file_name

IF @exists = 1
BEGIN
--set command for calling FSO deletefile method
--replace 1 with 0 if read-only files should NOT be deleted
SET @command = 'DeleteFile("'+@file_name+'", 1)'

--delete file
EXEC @error = sp_OAMethod @object, @command

IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc
END

END
--move to next record of image names
SELECT @runner = @runner + 1
END

--destroy FileSystem Object
EXEC @error = sp_OADestroy @object
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END


--for bulk deletion of files, the above code is time-consuming, since it must run through every one of the deleted files.

--HTH--