SQL Server 2005 - deleting images from a database
Posted on 2007-10-10
Database: SQL Server 2005
We are currently trying to build a SQL statement to delete image records in a database, related to other tables related via foreign keys. We are successful in deleting regular data from tables, but when it comes to deleting from tables with images stored inside of them, the statement is timing out on us. I have tried many different solutions (running the SQL code in the code-behind of the windows service, increasing/decreasing the rowcount, but still have the problem of timing out.)
This query is built into a stored procedure, and being called by a windows service that we have created as well.
Here is the error we are receiving:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Here is the query being ran:
SET ROWCOUNT 50
(SELECT DISTINCT ID FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs))
DELETE FROM Images_Table WHERE ID NOT IN (SELECT num FROM CurrentIDs)