SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error message during deletion


Error message during deletion

Author
Message
kk.86manu
kk.86manu
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 303
Hi All,

I am trying to delete a record from a table using a stored procedure.While executing the SP i am getting the below error.

"The query processor ran out of stack space during query optimization. Please simplify the query."

What could be the reason?

Please help
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5509 Visits: 35456
kk.86manu (5/7/2013)
Hi All,

I am trying to delete a record from a table using a stored procedure.While executing the SP i am getting the below error.

"The query processor ran out of stack space during query optimization. Please simplify the query."

What could be the reason?

Please help


please post details of your proc
ddl for the table you are deleting from, including indexes and FKs
number of rows in table

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1101 Visits: 1336
What version of SQL Server you are in?
It is a known issue SQL Server 2005 SP2. There is a ptch out to fix this issue.
But if you are in some other version. you have to look back at the code to see what you can optimize. Because What I believe that you have a very -2 complex and big stored proc which is trying to delete something.

Divide your sql's into small statements, use temp tables to store intermediate data etc..
kk.86manu
kk.86manu
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 303
Hi Livingston,

The procedure is a simple delete statement on a table based on ID column and the total number of rows is 25.

Table in which i am trying to delete is referenced by foreign key(6 foreign key references).

Please let me know if this is causing the issue.If that is the case how to overcome.

Kindly let know your views.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search