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 |
|
anuprshetty
Starting Member
5 Posts |
 Posted - 2007-06-13 : 03:19:07
|
| Hi...I have written a stored procedure with number of inserts as the input variables which will input those many rows into my execution table... When i try to input more than 31 rows it gives me a error shown below.... I have a trigger attached to the execution table where i am inserting the rows... I am unable to sort out the error..If anyone knows whats the problem Please reply.... Thank you....Msg 217, Level 16, State 1, Procedure runtime_insert_elevator_table, Line 105Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).anup |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-13 : 03:36:53
|
| Is the trigger itself inserting into the same table as the trigger is?Peter LarssonHelsingborg, Sweden |
 |
|
|
anuprshetty
Starting Member
5 Posts |
Posted - 2007-06-13 : 05:23:37
|
Sorry i didnt get your point... I hav sent the code below.. Here elevator_run is the table where my stored procedure is inserting rows into the table..and the trigger is inserting the rows into log_elevator_run...Thank you...----------------------------------------------------------------create trigger trigger_elevator_run on elevator_run for insert as insert into log_elevator_run(user_id,user_name,work_station_id,time_of_insert)values(user_id(),user_name(),host_id(),getdate())--------------------------------------------------------------quote:
Originally posted by Peso
Is the trigger itself inserting into the same table as the trigger is?Peter LarssonHelsingborg, Sweden
anup |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-13 : 05:35:02
|
| Is there any trigger defined on log_elevator_run table? Also post the code for SP - runtime_insert_elevator_table.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
anuprshetty
Starting Member
5 Posts |
Posted - 2007-06-13 : 07:11:36
|
| No no trigger on that... I found the answer.... But thanks for sharing the thought....The answer is RemarksEach time a stored procedure calls another stored procedure or executes managed code by referencing a common language runtime (CLR) routine, type, or aggregate, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.When @@NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level.------------------------------------------------------------------Hey guys is there anyway by which i would be able to make the stored procedure to insert more than 32 rows at one execution of it....anup |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-06-13 : 07:34:21
|
| "Hey guys is there anyway by which i would be able to make the stored procedure to insert more than 32 rows at one execution of it...."Yes. Try approaching your problem in a SET BASED manner instead of an iterative one.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
anuprshetty
Starting Member
5 Posts |
Posted - 2007-06-13 : 09:13:17
|
| Thanks for the guidence and links....... It was very useful......anup |
|
|
|
|
|
|
|
|
Excceed maximum...