December 19, 2014 at 8:33 am
Hi All
Ending up with the error "Msg 50000, Level 11, State 11, Procedure Usp_CopyProcedure, Line 724"
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
FirstName varchar(30),
LastName varchar(30)
);
INSERT @MyTableVar
EXEC dbo.Usp_CopyProcedure
Msg 50000, Level 11, State 11, Procedure Usp_CopyProcedure, Line 724
How do i get rid of this issue, appriciate your help
Regards
John
December 19, 2014 at 12:40 pm
What's at line 724 of Usp_CopyProcedure?
Gerald Britton, Pluralsight courses
December 19, 2014 at 12:56 pm
i can tell you it's an explicit error that someone coded to capture in your procedure, where someone is using the RAISERROR('some string',16,1).
as noted, read the proc and jump to line 724.
Lowell
December 19, 2014 at 12:56 pm
i can tell you it's an explicit error that someone coded to capture in your procedure, where someone is using the RAISERROR('some string',11,11).
as noted, read the proc and jump to line 724.
Lowell
December 22, 2014 at 2:27 am
Hi
Thanks for your reply, yes RAISERROR statement is there at that particular statement but how can i overcome this issue
Thanks
John
December 22, 2014 at 2:37 am
Look at what conditions would cause the RAISERROR line to be executed.
Open up the proc, go to line 724 and work out, from the code, what it does and what would make the RAISERROR run
It's a custom error, it's something that the developer put into that procedure, it's not something we can help you with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 6:23 am
Yes, actually the inner procedure is inserting the procedure1 output into a temporary table 1 and again the procedure2 is trying to insert the resultset into temporary table 2, so we are getting an error saying that
"An INSERT EXEC statement cannot be nested."
December 22, 2014 at 6:26 am
Well, there you have it. INSERT .. EXEC cannot be nested.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 6:59 am
I agree with you, it used to work, recently we have added a column to the temp table in prod env, then it is giving the exception, will work on the oter alternatives to avoid this, appriciate if can tell us something
Thanks
John
December 22, 2014 at 7:13 am
If it worked previously, then you weren't trying to nest INSERT ... EXEC as they cannot be nested under any circumstances. You're going to have to figure out an alternative way of doing things that doesn't require nesting them
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2014 at 12:55 am
convert your SP into Table-Valued function to use nested insert
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply