WCF SQL adapter error: System.Data.SqlClient.SqlException: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Posted: April 18, 2011  |  Categories: BizTalk Uncategorized

Last week I started to get this error in one of the solutions that I had developed. The solution had been working fine and all of a sudden in UAT this error started to occur.

The custom database that was written by a third party had been modified . The stored procedure that the WCF SQL adapter was using had the following extra lines;

—more above here

— Create “Allocate” Scan

— collect data

select @DT =GetDate(), @ticket_nbr = awb, @specified_dtrcde = account, @specified_rate = rateCode

from internationalBooking with(nolock)where intBookingID = @intBookingID

exec allocate

@ticket_nbr,‘A’, @DT, @DT,@specified_dtrcde, @specified_rate,null,‘International Job Booking’,null,null,null,null, @seq OUTPUT

— more below here

This addition seemed to fairly innocuous but when I opened SQL management studio and looked at the dependencies,  sure enough the stored procedure was nested as a far as the eye can see. Even so I could not find 32 nested levels. I think that the error that I got might be bit misleading but the critical point is that the WCF SQL adapter was no longer compatible with the stored procedure.

I thought this was because the WCF SQL adapter was  not able to generate metadata from a stored procedure even though it is perfectly valid and can be accessed and run without a hitch from the SQL Server Management Studio or directly from code. Furthermore to my chagrin I found that the classical SQL adapter did not have problem with the same stored procedure. This was not viable solution for us because the customer wanted to stop using the classical SQL adapter and we had already invested too much time in developing the a solution using the WCF SQL adapter.

Thus, I decided to modify the store offending stored procedure to revert the execution of SET FMTONLY ON that the adapter does before the execution of the procedure.  See http://thoughtsofmarcus.blogspot.com/2010/11/calling-stored-procedures-from-biztalk.html for the original example of this using this trick. The changed stored procedure was;

—more above here

— Play nice with BIZTALK

DECLARE @CheckFmt bit;

SELECT @CheckFmt = 0 WHERE 1=1

IF @CheckFmt ISNULL

SETFMTONLYOFF;

— Create “Allocate” Scan

— collect data

select @DT =GetDate(), @ticket_nbr = awb, @specified_dtrcde = account, @specified_rate = rateCode

from internationalBooking with(nolock)where intBookingID = @intBookingID

IF @CheckFmt ISNOTNULL

exec allocate

@ticket_nbr,‘A’, @DT, @DT,@specified_dtrcde, @specified_rate,null,‘International Job Booking’,null,null,null,null, @seq OUTPUT

IF @CheckFmt ISNULL

SETFMTONLYON;

— End of “Play nice with BIZTALK”

— more below here

To my delight when I now executed the changed stored procedure  from the BizTalk solution i did not get this error and the the nested stored procedures executed at runtime.

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top