help me deleting bulk records and images at once??
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..
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?
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
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)'