Hi all,

I have a BizTalk application that calls a SQL Stored proc using WCF-Custom port.Typed request and response schemas for stored proc were extracted by 'ConsumeAdapterServiceWizard'.

Now when I'm running the BizTalk application it works fine for one set of input values.But for another set of input values , the same BizTalk application gives warning and the instance gets suspended.

This is the final xLang error which comes when instance gets suspended:

Event Type: Error
Event Source: XLANG/s
Event Category: None
Event ID: 10034
Date:  20/05/2011
Time:  10:12:32
User:  N/A
Computer: myComp
Description:
xlang/s engine event log entry: Uncaught exception (see the 'inner exception' below) has suspended an instance of service 'GetConsumption(2752c93f-c330-19e5-8ffc-d3540a83bd81)'.
The service instance will remain suspended until administratively resumed or terminated.
If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.
InstanceId: a2dd07bc-edae-44b0-a276-eb5354848df2
Shape name:
ShapeId:
Exception thrown from: segment -1, progress -1
Inner exception: An error occurred while processing the message, refer to the details section for more information
Message ID: {BAF43CD3-99F5-4F38-98CE-14E83F1AEFD4}
Instance ID: {DD0DA4E4-812C-4E0C-9C5A-A61C295BF938}
Error Description: Microsoft.ServiceModel.Channels.Common.AdapterException: The ResultSet returned as part of the Typed Stored Procedure or Typed Polling invocation did not match the metadata available. If this Stored Procedure or Polling Statement can return a variable number of result sets, consider using the un-typed Stored Procedure or un-typed Polling operation instead.
   at Microsoft.Adapters.Sql.GenericReaderWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
   at System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkResponseMessage(IBaseMessageFactory factory, IAdapterConfigInboundMessageMarshalling marshaling, Message wcfMessage)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

       
Exception type: XlangSoapException
Source: Microsoft.XLANGs.BizTalk.Engine
Target Site: Void VerifyTransport(Microsoft.XLANGs.Core.Envelope, Int32, Microsoft.XLANGs.Core.Context)
The following is a stack trace that identifies the location where the exception occured

   at Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.VerifyTransport(Envelope env, Int32 operationId, Context ctx)
   at Microsoft.XLANGs.Core.Subscription.Receive(Segment s, Context ctx, Envelope& env, Boolean topOnly)
   at Microsoft.XLANGs.Core.PortBase.GetMessageIdForSubscription(Subscription subscription, Segment currentSegment, Context cxt, Envelope& env, CachedObject location)
   at Microsoft.XLANGs.Core.PortBase.GetMessageId(Subscription subscription, Segment currentSegment, Context cxt, Envelope& env, CachedObject location)
   at GetSmartMeterEnergyConsumption.CallGetSmartMeterEnergyConsumption.segment1(StopConditions stopOn)
   at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s, StopConditions stopCond, Exception& exp)

        For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Please note that the input values for which we get this error in BizTalk, we get result if we run it from SQL management studio.

About the SQL stored proc: The stored proc calls several sub stored procs based on the various input values. Also for enabling BizTalk to extract the strongly typed metadata , there is use of 'FMTONLY' flag in it, as it uses temp tables.

Please help me what this error means? Is it possible that issue is with stored proc, as I cant find a reason to think that the problem is with BizTalk application, as we are able to get the response for one set of values?

Please mention if further information is required.

Thanks 

Sounds like the result structure of your SP changes depending on input.  Is that the case?  Go into the BT admin console and look at the actual message that has been sent back.  Compare that to what you're expecting. 

Kind Regards,

-Dan

Thanks Dan,

 

Yes , the response structure of the sp changes, depending upon input.

The scenario is like this:

We have a stored proc which internally have multiple sub procs in 'If Else' based on a field called 'granularity' . The parameters returns by each sub procs are same (name and datatype).   Now to call this stored proc we have extracted the request and response schema using BizTalk WCF Adapter metadata wizard.

Now when we are running a test case for granularity1 . The sp returns the result properly. The difference in the output of granulairty 1 and other are that we get 'null' for one filed (means that field does not come in the reponse) called 'measure' in case of granularity1. Which is as per the funtionality.

And for all other granulairty we are facing the above mentioned issue, so I looked in the response schema and looked the datatype of the field 'measure' which was returned as 'null' in granularity1 case. Its datatype in  response schema is coming as 'xs:int' , but 'measure' is defined as varchar in the stored proc.

I dont understand why BizTalk Adapter metadata wizard extracting like this. And how it works for extraction.

Anyway, I changed the datatype of 'measure' to 'xs:string' manually in the extracted schema and used it in the BizTalk solution. But the same problem is still there.

One thing to notice here is sub stored proc for granularity1 come first in the 'If Else' of main stored proc, and this sub stored proc is the only one which returns 'null' for the 'measure' field. Is this may be the cause of error?

How does BizTalk WCF Adapter metadata wizard works if some field is returned as 'null' in the sp ? Does this wizard put data type 'int' by default for such values?

Please help. How can I make it work from BizTalk end?

 

Thanks in advance.

Hi Anoop,

As suggested by the error message

" The ResultSet returned as part of the Typed Stored Procedure or Typed Polling invocation did not match the metadata available. If this Stored Procedure or Polling Statement can return a variable number of result sets, consider using the un-typed Stored Procedure or un-typed Polling operation instead."

Have you tried Polling instead of TypedPolling

Support for Polling

http://msdn.microsoft.com/en-us/library/dd788416(v=bts.10).aspx

http://msdn.microsoft.com/en-us/library/dd788137(v=bts.10).aspx

Thanks for the reply Rohit,

Yes, I've tried using the un-typed stored procedure and it works fine.

Please note we are using WCF-SQL adapter here. Here we have to use typed stored procedure option while extracting metedata, as we need to map the stored proc response to other format. And we cant use xslt to do that because of latancy issues.

In short, we cant make changes in the existing stored proc and we must use the strongly typed stored proc . Considering this issue, any suggestion how can I make it work in BizTalk?

Hi Anoop,

There is one workaround mentioned here to use the mapper in case of un-typed stored procedure. I hope it would help you in resolving this issue:

http://blogs.msdn.com/b/adapters/archive/2008/09/30/multiple-ways-to-generate-metadata-execute-stored-procedures-in-the-sql-adapter.aspx

Limitations: The result sets are loosely typed (being a DataSet[]), and in BizTalk, are not helpful if you want to use the Mapper.

Workarounds: What you could do is, execute the procedure once, dump the XML message to a file location, and open it in notepad. Select the <schema> node within the result set section in the return parameter, copy-paste it into a new file, and save it with the .xsd extension. You now have a schema which you can deploy in your BizTalk orchestration/project. Also, use the Message Template feature in the WCF-Custom/WCF-SQL port configuration, using a XPath query to only select out the data (at runtime) for that particular result set (matching the XSD which you deployed); ignoring the other result sets (if any) and out parameters. You now have a XML blob being submitted to BizTalk, which conforms to the XSD which you deployed.

Anoop,

In the same link you can see the limitation of the Typed polling and as per these limitations it seems that you can't use the typed polling for your scenarion, as you mentioned above:

About the SQL stored proc: The stored proc calls several sub stored procs based on the various input values. Also for enabling BizTalk to extract the strongly typed metadata , there is use of 'FMTONLY' flag in it, as it uses temp tables.

Limitations:

·      These operations cannot be used to execute “complicated” Stored Procedures – for example, a hypothetical procedure which returns multiple result sets (from potentially different tables) within a loop. This is because the adapter won’t be able to figure out which result set (at runtime) needs to be serialized as which complex type (TypedProcedureResultSet1, or TypedProcedureResultSet2, or something else).

·      These operations won’t work for Stored Procedures, when in the procedure code, a temporary table is created, and then, one of the returned result sets is obtained by doing a SELECT on that temporary table. The reason being, when the SET FMTONLY ON option is used, no temporary tables are created. However, when the SQL execution engine comes across the line SELECT * FROM #temptable, it throws an error, (something to the effect of it not finding an artifact named #temptable), since it never created this artifact in the first place (since SET FMTONLY ON is not supposed to make any changes on the server).

You can, however, use XmlPolling and return whatever you like.  FOR XML still works great and allows for arbitrarily complex result sets.  I would consider using that instead.  You can even use it to formulate the XML the exact way you want (different sub-trees) or completely different result sets with a map for each set available. 

The best part is you can use the classic SQL Adapter wizard to generate the artifacts (schemas) and then just use the new one to run them.  Check this thread out: http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/fbdebe36-16db-43f0-b68f-88648b39b74f

 

Kind Regards,

-Dan

Hello Anoop, 

Did you get the solution to your issue? as I am facing the same issue but the resolution mentioned here by Rohit is not working for me. 

If this is a new issues, you should start a new thread describing you issues including any error messages you're getting.

Hello Boatseller, 

I have already done that at : 

https://social.msdn.microsoft.com/Forums/en-US/8b594a20-719a-4dd5-876b-ca50408e1896/the-resultset-returned-as-part-of-the-typed-stored-procedure-or-typed-polling-invocation-did-not?forum=biztalkgeneral#8b594a20-719a-4dd5-876b-ca50408e1896 

but no reply till now