locked
executescalar on WCF Sql Adapter generates error of schema not found RRS feed

  • Question

  • 1. Added a new generated item to consume sql adapter and chose ExecuteScalar.

    2. Created an orchestration to consume that service.

    3. Perform a query with a COUNT(*).

    Upon execution, this error occurs:

    There was a failure executing the send pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML assembler" Send Port: "WcfSendPort_SqlAdapterBinding_Multiple_Custom" URI: "mssql://BizTalk23//NtslData?" Reason: This Assembler cannot retrieve a document specification using this type: "http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar".


    Here's the schema generated when the generated item is added, which gets deployed successfully:

      <?xml version="1.0" encoding="utf-16" ?>
    - <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:tns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/ " elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    - <xs:annotation>
    - <xs:appinfo>
      <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">GenericOperation</fileNameHint>
      </xs:appinfo>
      </xs:annotation>
    - <xs:element name="ExecuteScalar">
    - <xs:annotation>
    - <xs:documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">GenericOp/ExecuteScalar</doc:action>
      </xs:documentation>
      </xs:annotation>
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="1" maxOccurs="1" name="Query" nillable="true" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
    - <xs:element name="ExecuteScalarResponse">
    - <xs:annotation>
    - <xs:documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">GenericOp/ExecuteScalar/response</doc:action>
      </xs:documentation>
      </xs:annotation>
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="ExecuteScalarResult" nillable="true" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:schema>

     

     

     

    Saturday, February 19, 2011 1:56 PM

Answers

  • Ok, so the problem is solved.

     

    1. There were two message types of the same type. By performing the search and removing the other types, the original error for this thread was removed.

     

    SELECT  [id] ,[itemid] ,[msgtype]
    FROM [BizTalkMgmtDb].[dbo].[bt_DocumentSpec]
    Where msgtype = 'http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar'

     

    2. When I attempted to simplify by creating a new orchestration, I forgot to change the operation name. This blog entry covers it well: http://blogs.msdn.com/b/biztalk_adapter_development/archive/2008/01/08/why-does-the-adapter-say-action-is-not-understood-even-though-i-am-using-the-binding-file-generated-by-the-consume-adapter-service-wizard.aspx .

    • Marked as answer by bogatiy Monday, February 21, 2011 2:39 PM
    Monday, February 21, 2011 2:38 PM

All replies

  • Hey,

    Can you check if your Schema is deployed ? This happens when your schema is not deployed which results in the messagetype "http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar" is not found by the assembler.

    Please deploy the schema and try. 

     

    Thanks.

    Please mark this answer if it helps.


    Sriram N http://srirambiztalks.wordpress.com/
    Saturday, February 19, 2011 2:51 PM
  • Thanks, yes I checked if it was deployed successfully and it is.

    There are two schemas deployed with GenericOperation, one with a root name of Execute Scalar and one with a root name of ExecuteScalarResponse (two way adapter).

     

    The orchestration first does a composite insert (to two tables), then attempts to perform the scalarexecute. The composite insert works fine.

     

    Saturday, February 19, 2011 3:01 PM
  • Can you check if you have multiple schemas of the messagetype deployed. This might also cause the problem?

    Check what is returned in this Query in ManagementDB. If it returns multiple, then that should be the problem.

    SELECT        target_namespace, date_modified

    FROM            bt_XMLShare

    WHERE        target_namespace = 'http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar'

    If you are not having XMLTransmit for a purpose, then you can use passthru.

     

    Thanks

    Please mark this answer if it helps.


    Sriram N http://srirambiztalks.wordpress.com/
    Saturday, February 19, 2011 3:25 PM
  • Ran that query and it returns zero results.

     

    When modified to use

     

    WHERE        target_namespace = 'http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/'

     

    then it returns more than one schema. This seems normal as I have several applications using that adapter and each has a port defined.

     

    When I try to use passthru the message won't go. Haven't tried to troubleshoot that yet, more focused on why the schema can't be found, or why that error message appears.

     

    I simplified the orchestration so it only calls out the two-way query and nothing else. This really didn't help much as it didn't get any new info, and the same error occurs. I started fresh with a new solution, project, all new artifacts, and deployed to a new application with new port definitions. Ultimately the error came out identical to what it was previously.

    Sunday, February 20, 2011 11:19 PM
  • It needs the XMLTransmit. When I try to use passthru it generates a transmission failure and the error is:

     

    The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_GenericTableOp_Custom" with URL "mssql://BizTalk23//NtslData?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Operation Name="ExecuteScalar" Action="GenericOp/ExecuteScalar" />
    </BtsActionMapping>" was not understood.

     

    The database query shown below finds four such message types, and one corresponding to my application. So it looks like everything deployed successfully.

     

    SELECT  [id] ,[itemid] ,[msgtype]
    FROM [BizTalkMgmtDb].[dbo].[bt_DocumentSpec]
    Where msgtype = 'http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar'

     

    Sunday, February 20, 2011 11:36 PM
  • Ok, so the error is caused by more than one message type in that database. Once I removed the other message types, the XML Transmit pipeline resolved the schema.I removed them by removing the assemblies installed by my other applications.

     

    However, now it's generating this message:

     

    The adapter failed to transmit message going to send port "WcfSendPort_SqlAdapterBinding_GenericTableOp_Custom" with URL "mssql://BizTalk23//NtslData?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action "<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <Operation Name="ExecuteScalar" Action="GenericOp/ExecuteScalar" />
    </BtsActionMapping>" was not understood.


    The schema and binding file are shown below:

     

    ---------SCHEMA FILE----------------------------------------------------

      <?xml version="1.0" encoding="utf-16" ?>
    - <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:tns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    - <xs:annotation>
    - <xs:appinfo>
      <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">GenericOperation</fileNameHint>
      </xs:appinfo>
      </xs:annotation>
    - <xs:element name="ExecuteScalar">
    - <xs:annotation>
    - <xs:documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">GenericOp/ExecuteScalar</doc:action>
      </xs:documentation>
      </xs:annotation>
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="1" maxOccurs="1" name="Query" nillable="true" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
    - <xs:element name="ExecuteScalarResponse">
    - <xs:annotation>
    - <xs:documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">GenericOp/ExecuteScalar/response</doc:action>
      </xs:documentation>
      </xs:annotation>
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="0" maxOccurs="1" name="ExecuteScalarResult" nillable="true" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:schema>

     

    -----BINDING FILE----------------------------------------

    <?xml version="1.0" encoding="utf-8"?>
    <BindingInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Assembly="Microsoft.BizTalk.Deployment, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Version="3.5.1.0" BindingStatus="NoBindings" BoundEndpoints="0" TotalEndpoints="0">
      <Timestamp>2011-02-20T19:01:04.0963677-05:00</Timestamp>
      <ModuleRefCollection />
      <SendPortCollection>
        <SendPort Name="WcfSendPort_SqlAdapterBinding_GenericTableOp_Custom" IsStatic="true" IsTwoWay="true" BindingOption="0">
          <Description>SendPort for SqlAdapterBinding.</Description>
          <TransmitPipeline Name="Microsoft.BizTalk.DefaultPipelines.XMLTransmit" FullyQualifiedName="Microsoft.BizTalk.DefaultPipelines.XMLTransmit, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Type="2" TrackingOption="None" Description="" />
          <PrimaryTransport>
            <Address>mssql://BizTalk23//NtslData?</Address>
            <TransportType Name="WCF-Custom" Capabilities="907" ConfigurationClsid="af081f69-38ca-4d5b-87df-f0344b12557a" />
            <TransportTypeData>&lt;CustomProps&gt;
      &lt;BindingType vt="8"&gt;sqlBinding&lt;/BindingType&gt;
      &lt;BindingConfiguration vt="8"&gt;&amp;lt;binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="Infinite" sendTimeout="00:01:00" maxConnectionPoolSize="100" encrypt="false" workstationId="" useAmbientTransaction="true" batchSize="20" polledDataAvailableStatement="" pollingStatement="" pollingIntervalInSeconds="30" pollWhileDataFound="false" notificationStatement="" notifyOnListenerStart="true" enableBizTalkCompatibilityMode="true" chunkSize="4194304" inboundOperationType="Polling" useDatabaseNameInXsdNamespace="false" allowIdentityInsert="false" acceptCredentialsInUri="false" enablePerformanceCounters="false" xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" /&amp;gt;&lt;/BindingConfiguration&gt;
      &lt;StaticAction vt="8"&gt;&amp;lt;BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&amp;gt;
      &amp;lt;Operation Name="ExecuteScalar" Action="GenericOp/ExecuteScalar" /&amp;gt;
    &amp;lt;/BtsActionMapping&amp;gt;&lt;/StaticAction&gt;
      &lt;UseSSO vt="11"&gt;0&lt;/UseSSO&gt;
      &lt;InboundBodyLocation vt="8"&gt;UseBodyElement&lt;/InboundBodyLocation&gt;
      &lt;InboundNodeEncoding vt="8"&gt;Xml&lt;/InboundNodeEncoding&gt;
      &lt;OutboundBodyLocation vt="8"&gt;UseBodyElement&lt;/OutboundBodyLocation&gt;
      &lt;OutboundXmlTemplate vt="8"&gt;&amp;lt;bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="xml"/&amp;gt;&lt;/OutboundXmlTemplate&gt;
      &lt;PropagateFaultMessage vt="11"&gt;-1&lt;/PropagateFaultMessage&gt;
      &lt;EnableTransaction vt="11"&gt;-1&lt;/EnableTransaction&gt;
      &lt;IsolationLevel vt="8"&gt;Serializable&lt;/IsolationLevel&gt;
      &lt;Identity vt="8" /&gt;
    &lt;/CustomProps&gt;</TransportTypeData>
            <RetryCount>3</RetryCount>
            <RetryInterval>5</RetryInterval>
            <ServiceWindowEnabled>false</ServiceWindowEnabled>
            <FromTime>2000-01-01T00:00:00</FromTime>
            <ToTime>2000-01-01T23:59:59</ToTime>
            <Primary>true</Primary>
            <OrderedDelivery>false</OrderedDelivery>
            <DeliveryNotification>0</DeliveryNotification>
            <SendHandler xsi:nil="true" />
          </PrimaryTransport>
          <SecondaryTransport>
            <Address />
            <TransportTypeData />
            <RetryCount>3</RetryCount>
            <RetryInterval>5</RetryInterval>
            <ServiceWindowEnabled>false</ServiceWindowEnabled>
            <FromTime>2000-01-01T00:00:00</FromTime>
            <ToTime>2000-01-01T23:59:59</ToTime>
            <Primary>false</Primary>
            <OrderedDelivery>false</OrderedDelivery>
            <DeliveryNotification>0</DeliveryNotification>
            <SendHandler xsi:nil="true" />
          </SecondaryTransport>
          <ReceivePipeline Name="Microsoft.BizTalk.DefaultPipelines.XMLReceive" FullyQualifiedName="Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Type="1" TrackingOption="None" Description="" />
          <ReceivePipelineData xsi:nil="true" />
          <Tracking>0</Tracking>
          <Filter />
          <OrderedDelivery>false</OrderedDelivery>
          <Priority>5</Priority>
          <StopSendingOnFailure>false</StopSendingOnFailure>
          <RouteFailedMessage>false</RouteFailedMessage>
          <ApplicationName xsi:nil="true" />
        </SendPort>
      </SendPortCollection>
      <DistributionListCollection />
      <ReceivePortCollection />
      <PartyCollection xsi:nil="true" />
    </BindingInfo>

     

     

     

     

    Monday, February 21, 2011 12:46 AM
  • Hey,

    Please do not post 2 questions in the same thread. This will not help/mislead people who search for the second query as the thread title points to the first issue.

    For this issue,

    •  You orchestration logical port name should match the "Name" part of your ActionMapping.Please change your logical port name to "ExecuteScalar".

                    <BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                    <Operation Name="ExecuteScalar" Action="GenericOp/ExecuteScalar" />
                    </BtsActionMapping>

    Please read the following post regarding this.

    http://blogs.msdn.com/b/biztalk_adapter_development/archive/2008/01/08/why-does-the-adapter-say-action-is-not-understood-even-though-i-am-using-the-binding-file-generated-by-the-consume-adapter-service-wizard.aspx

     

    Thanks.

    Please mark this as answer if it helps.


    Sriram N http://srirambiztalks.wordpress.com/
    Monday, February 21, 2011 2:12 AM
  • Ok, so the problem is solved.

     

    1. There were two message types of the same type. By performing the search and removing the other types, the original error for this thread was removed.

     

    SELECT  [id] ,[itemid] ,[msgtype]
    FROM [BizTalkMgmtDb].[dbo].[bt_DocumentSpec]
    Where msgtype = 'http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalar'

     

    2. When I attempted to simplify by creating a new orchestration, I forgot to change the operation name. This blog entry covers it well: http://blogs.msdn.com/b/biztalk_adapter_development/archive/2008/01/08/why-does-the-adapter-say-action-is-not-understood-even-though-i-am-using-the-binding-file-generated-by-the-consume-adapter-service-wizard.aspx .

    • Marked as answer by bogatiy Monday, February 21, 2011 2:39 PM
    Monday, February 21, 2011 2:38 PM
  • Just some cleanup/closure.

     

    Once working I modified the adapter to use passthru on the send, and it works fine. Then I added back the message types from the other applications (so there'd be more than one message type of that type). Once I did that it was interesting that finally the error message returned was very helpful, and if it had been the one returned originally the problem would have been much easier to solve. Here it is:

     

    There was a failure executing the response(receive) pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Send Port: "WcfSendPort_SqlAdapterBinding_GenericTableOp_Custom" URI: "mssql://BizTalk23//NtslData?" Reason: Cannot locate document specification because multiple schemas matched the message type "http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/#ExecuteScalarResponse".

     

    Though this came in on the response since I left the receive pipeline as xml receive. when attempting to use passthru it couldn't parse and process the message further. Enough ... onto the next problem.

    • Edited by bogatiy Monday, February 21, 2011 2:53 PM clarified difference between send/receive error
    Monday, February 21, 2011 2:52 PM