September 9, 2014 at 3:13 pm
When I run this script on my local dev server from SSMS, it works as it should.
When I run this script from SSMS on my database server in the cloud, it adds 32 records. Any suggestions?
DECLARE @NewFirmID INT;
EXECUTE procAddFirmWithAbbreviationNameStateEmail @FirmAbbreviation = 'PRE', @FirmName = 'PRO REAL ESTATE GROUP ', @State = 'CA', @ContactEmail = 'jdoe@pro.com',
@FirmID=@@IDENTITY;
SELECT @NewFirmID = @@IDENTITY
INSERT INTO ...
___________________
This is the proc used:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[procAddFirmWithAbbreviationNameStateEmail]
@FirmID int OUTPUT,
@FirmAbbreviation nvarchar(10),
@FirmName nvarchar(10),
@State nvarchar(20)=Null,
@ContactEmail nvarchar(50)=Null
AS
INSERT INTO [dbo].[Firms]
([FirmAbbreviation]
,[FirmName]
,[State]
,[ContactEmail])
VALUES
(@FirmAbbreviation
,@FirmName
,@State
,@ContactEmail)
Here's the warning:
Msg 217, Level 16, State 1, Procedure procAddFirmWithAbbreviationNameStateEmail, Line 65
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
September 9, 2014 at 3:21 pm
TBIG (9/9/2014)
When I run this script on my local dev server from SSMS, it works as it should.When I run this script from SSMS on my database server in the cloud, it adds 32 records. Any suggestions?
DECLARE @NewFirmID INT;
EXECUTE procAddFirmWithAbbreviationNameStateEmail @FirmAbbreviation = 'PRE', @FirmName = 'PRO REAL ESTATE GROUP ', @State = 'CA', @ContactEmail = 'jdoe@pro.com',
@FirmID=@@IDENTITY;
SELECT @NewFirmID = @@IDENTITY
INSERT INTO ...
___________________
This is the proc used:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[procAddFirmWithAbbreviationNameStateEmail]
@FirmID int OUTPUT,
@FirmAbbreviation nvarchar(10),
@FirmName nvarchar(10),
@State nvarchar(20)=Null,
@ContactEmail nvarchar(50)=Null
AS
INSERT INTO [dbo].[Firms]
([FirmAbbreviation]
,[FirmName]
,[State]
,[ContactEmail])
VALUES
(@FirmAbbreviation
,@FirmName
,@State
,@ContactEmail)
Here's the warning:
Msg 217, Level 16, State 1, Procedure procAddFirmWithAbbreviationNameStateEmail, Line 65
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
First of all you should not use @@IDENTITY. You should instead be using SCOPE_IDENTITY.
The next thing is how is that procedure inserting 32 rows? There is only a single insert statement with values so it can't possibly be inserting more than 1 row. Do you have an insert trigger on Firms?
Last but not least is it looks like you are expecting some output from your procedure. However you don't have anything defined as an output parameter. This code all looks very interdependent and extremely brittle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2014 at 8:16 pm
As you can see, this is a quick fix, stop gap bit of script. It's just something to patch us through until we do it right.
I've got a little mystery to solve.
Thanks!
September 10, 2014 at 12:45 am
TBIG (9/9/2014)
As you can see, this is a quick fix, stop gap bit of script. It's just something to patch us through until we do it right.I've got a little mystery to solve.
Thanks!
If the only thing running is an insert into a table with values clause, there is no mystery here. There is a trigger on the table - period. Now, figuring out why that is causing issues (and especially hitting the nesting level, which could involve other triggers or firing triggers on same table, etc) could be difficult!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 10, 2014 at 1:15 am
Msg 217, Level 16, State 1, Procedure procAddFirmWithAbbreviationNameStateEmail, Line 65
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Its self explanatory, you should better check the procedure source code which is running on the cloud.
as suggested by others check the trigger on this table. to clear your doubts, simple insert the record straight into the table i.e without using procedure by disabling the triggers on that table.
and please do it on the local dev machine not on to the cloud if its your production server.
September 11, 2014 at 2:48 am
One other thing to check,
In the script that CREATES the stored procedure, have you by chance got a call to the same stored procedure.
Something like
create procedure a (@params etc )
as
select....or whatever
exec procedure a
GO
Then when you run the procedure it may be calling itself recursively.
It is quite easy to do this by accident, especially if you miss the BEGIN/END off a stored procedure definition and have inadvertently added attest call of the procedure to the end of the procedure definition.
To avoid that possibility I always use:
create procedure a (@params etc )
as
BEGIN
select....or whatever
END
GO
exec procedure a
GO
Hope that helps
Mike John
September 11, 2014 at 4:32 am
Removed duplicated post.
September 11, 2014 at 7:16 am
Mike John (9/11/2014)
One other thing to check,In the script that CREATES the stored procedure, have you by chance got a call to the same stored procedure.
Something like
create procedure a (@params etc )
as
select....or whatever
exec procedure a
GO
Then when you run the procedure it may be calling itself recursively.
It is quite easy to do this by accident, especially if you miss the BEGIN/END off a stored procedure definition and have inadvertently added attest call of the procedure to the end of the procedure definition.
To avoid that possibility I always use:
create procedure a (@params etc )
as
BEGIN
select....or whatever
END
GO
exec procedure a
GO
Hope that helps
Mike John
It is the batch separator (GO by default) that is helping you here. The begin/end are just noise as they are not required and do not end the stored procedure code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2014 at 7:27 am
Mike John (9/11/2014)
It is quite easy to do this by accident, especially if you miss the BEGIN/END off a stored procedure definition
Nothing to do with the BEGINS and ENDs there.
create procedure a
as
BEGIN
SELECT * FROM sys.objects AS o
END
exec a
GO
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
September 11, 2014 at 7:44 am
Yes, there was other stuff at the end of th CREATE proc...
Thanks!
September 11, 2014 at 8:58 am
Thanks for pointing out my incorrect assumption - so used to including both the BEG END and GO I had forgotten which avoided the issue!
Mike John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply