Problem with SQLEXECUTE

Question

I am getting this error. 

"It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Xml.XmlException: Start element 'SQLSTATEMENT' expected. Found element 'ns0:SQLSTATEMENT' from namespace 'http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE'."

 I am trying to use SQLEXECUTE.  No problem with the sample on BT 2009.  When I create my own project and map a simple schema to the SQLEXECUTE schema if fails.  Any help would be appreciated.  

<?xml version="1.0" encoding="utf-16" ?>

- <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://WorkOrderService.InputForSQLEXECUTE" targetNamespace="http://WorkOrderService.InputForSQLEXECUTE" xmlns:xs="http://www.w3.org/2001/XMLSchema">

- <xs:element name="SqlInput">

- <xs:complexType>

- <xs:sequence>

  <xs:element name="WONumber" type="xs:string" />

  </xs:sequence>

  </xs:complexType>

  </xs:element>

  </xs:schema>

Details ....

Event Type: Warning
Event Source: BizTalk Server 2009
Event Category: (1)
Event ID: 5743
Date:  10/29/2009
Time:  3:44:09 PM
User:  N/A
Computer: lmsbizd03.til.csu.org
Description:
The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_SQLEXECUTE_OperationGroup_Custom" with URL "oracledb://gisdev". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Xml.XmlException: Start element 'SQLSTATEMENT' expected. Found element 'ns0:SQLSTATEMENT' from namespace 'http://Microsoft.LobServices.OracleDB/2007/03/SQLEXECUTE'.

Server stack trace:
   at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
   at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
   at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)".




Martin Honnen from a previous post says you can apply this style sheet to a to the map.  Does anyone know how to do this


<xsl:stylesheet
  version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:template match="*">
    <xsl:element name="{local-name()}" namespace="{namespace-uri()}">
      <xsl:apply-templates select="@* | node()"/>
    </xsl:element>
  </xsl:template>

  <xsl:template match="@* | text() | processing-instruction() | comment()">
    <xsl:copy/>
  </xsl:template>
</xsl:stylesheet>


Thanks!


After doing some more research it appears that you have to do another map prior to this applying this stylesheet
from basic schema --> basic schema(stripped of ns:0) 

Answer 1

Hi,

->"Martin Honnen from a previous post says you can apply this style sheet to a to the map.  Does anyone know how to do this"
Could you give us the previous post's link, you can also inform Martin Honnen about your issue.

Regards

---

This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support

Answer 2

SQLEXECUTE message generated by your code is not understood by the adapter fopr some unknown reason.
You have to transform it to the form understood by the adapter. 
Put the XSLT transform from Martin Honnen into a separate file, and create a custom map as explained here 
http://biztalkland.blogspot.com/2007/09/custom-xslt-in-biztalk-maps.html

Then call this map to constract a new "good" SQLEXECUTE message, and submit the message to the Oracle adapter.