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.
