You may have faced problem like this before also, when you were trying to delete a single/multiple record from a table. But have you got the solution for it?
Here is the problem I faced recently with SQL Server 2005 SP2 and the way I handled it after spending enough time on reading MSDN, various articles?
We have one table called “User” which is referenced by nearby 500 tables as well as self-referenced. Now when I tried to run a simple DELETE statement with primary key ID, I keep getting the stack overflow error (as mentioned in the Title). I was completely blank after reading MSDN, articles; I got no resolution anywhere.
Here are few reasons I have pointed out that could be the possibilities for this problem…
- Recursion during query optimization. Ref: http://support.microsoft.com/kb/945896
- The problem lies in number of Foreign Keys. In this scenario, the user table is referenced by many tables.Ref: http://www.sqldev.org/sql-server-database-engine/internal-query-processor-error-server-msg-8621-96775.shtml
- In some extreme situations stack size may become limit for given query. Ref: http://msdn.microsoft.com/en-us/library/aa226411(SQL.80).aspx
I used the traditional way to solve this problem. It could not be the correct way to resolve this, we can discuss on this as well as if I ‘get any resolution I’ll post it here.
- Disable all the referencing Foreign Keys. You can use the custom script or the below script. In below you will need to update the referenced entity.
DECLARE @FKName VARCHAR(255) DECLARE @MainEntity VARCHAR(255) DECLARE @SQLString VARCHAR(4000) DECLARE cur_FK CURSOR FOR SELECT name FKName, OBJECT_NAME(parent_object_id) MainEntity FROM sys.foreign_keys WHERE OBJECT_NAME(referenced_object_id) = 'User' OPEN cur_FK FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString = 'ALTER TABLE ['+ @MainEntity + '] CHECK CONSTRAINT [' + @FKName +']' --PRINT @SQLString EXEC (@SQLString) FETCH NEXT FROM cur_FK INTO @FKName, @MainEntity END CLOSE cur_FK DEALLOCATE cur_FK
- Run the Delete statement.
- Enable all the Foreign Keys. You can use the same script mentioned above just change the “NOCHECK” keyword to “CHECK”.
I hope this will help you all. Share your ideas to handle this problem.