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.
| 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 foobar11i 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 SQL2005Maximum 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 advanceAzam. |
|
|
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 foobar11i 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 SQL2005Maximum 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 advanceAzam.
|
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
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 opinionBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
|
|
|
|
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.aspxwhich 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. |
|
|
|
|
|
|
|
|
cascade delete (32 nested levels issue)