Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 cascade delete (32 nested levels issue)

Author  Topic 

jaanazam
Starting Member

5 Posts

Posted - 2011-07-27 : 11:21:08
GreySky,

thanks, your solution really got me going (the original one might fail due to too many nested subqueries). I added a "tablename"-column to the temptable, so cycles can be detected when setting the child-criteria (a simple subquery checking whether the current row has already been picked up on a lower call-level). Also, by casting the pk value to varchar (instead of int) I was able to mix all kinds of primary key types (guids, etc).

-----------------------------------------------
Hi foobar11

i need to delete the records in child tables which references the id of the parent table.
i used the above sp : "spCascadeDeleteLong".
it runs good but giving the following error due to nested levels exceeding 32
could you please paste the new version of code which solves the problem of nested levels as iam using the SQL2005

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


its very urgent i need to solve this issue.
expecting the quick response from you.

thanks in advance

Azam.

tomero
Starting Member

4 Posts

Posted - 2012-04-04 : 10:39:34
Hi Jaanazam,
I'm having the same problem.
Can you please attach the code that solved the 32 nested queries problem.
Thanks a lot.
Tomer.
quote:
Originally posted by jaanazam

GreySky,

thanks, your solution really got me going (the original one might fail due to too many nested subqueries). I added a "tablename"-column to the temptable, so cycles can be detected when setting the child-criteria (a simple subquery checking whether the current row has already been picked up on a lower call-level). Also, by casting the pk value to varchar (instead of int) I was able to mix all kinds of primary key types (guids, etc).

-----------------------------------------------
Hi foobar11

i need to delete the records in child tables which references the id of the parent table.
i used the above sp : "spCascadeDeleteLong".
it runs good but giving the following error due to nested levels exceeding 32
could you please paste the new version of code which solves the problem of nested levels as iam using the SQL2005

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


its very urgent i need to solve this issue.
expecting the quick response from you.

thanks in advance

Azam.



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 10:53:12
What do you mean by Nested Queries?

Or do you mean 32 levels of parent Child Cascading Deletes?

Cascading Deletes/Updates are just a plain idea in my opinion



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

tomero
Starting Member

4 Posts

Posted - 2012-04-05 : 03:06:44
Exactly.
SQL server has a limitation of 32 levels of nested parent Child Cascading Deletes.
GreySky's sproc works fast but I guess I have some FK cascading cycles in my DB and SQL server won't allow me to complete the run.
Somebody mentioned that you can add columns to the temp table in the code above to check if we already visited this field so no cycles can interrupt.
I was wondering if somebody has this final version or can explain better what's the adjustments needed.
Thanks a lot.
Tomer.

quote:
Originally posted by X002548

What do you mean by Nested Queries?

Or do you mean 32 levels of parent Child Cascading Deletes?

Cascading Deletes/Updates are just a plain idea in my opinion



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/




Go to Top of Page

tomero
Starting Member

4 Posts

Posted - 2012-04-05 : 10:48:20
Well,I Wanted to update that my problem was solved.

I had some circular FK references which overflowed the 32 nested level of FK references allowed by SQL server. (obviously)
As I fear this is the case for many users on a big product DB I thought it might be useful to post it here.

What I did was to run the sproc in :
http://blogs.msdn.com/b/sqlazure/archive/2010/07/01/10033575.aspx
which checks for circular FK references.

Than I decided which are the FK in each circle that I need to break (you might check to see the one with the least amount of rows).
Self references are not handled anyway so no point in deleting those.

Then I ran the sproc mentioned in this topic successfully (finally)
And all that remains is to delete the orphaned records due to the FK being deleted and then to enable the FKs again.

This small query did the trick:
SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN (SELECT <primary_key_column> FROM <primary_key_table>);

Hope that helps someone and thanks for all the help.
Tomer.
Go to Top of Page
   

- Advertisement -