none
SQL Adapter and Updategram or better RRS feed

  • Question

  • I have a small problem, so if someone can help...

     

    I am working with a client that will not allow stored procedures in their databases.

     

    So any access to and from SQL 2005 and 2008 must be done through select,updates,insert,delete.

     

    Is it safe to say that my best bet here is to use the updategrams or is there a better way?

     

    I am having trouble with updategrams in that they just dont work.

     

    The adapter failed to transmit message going to send port "TelDigSQLPort" with URL "SQL://dycwdvvm-f8io40/TelDigData/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80004005" Description="All updategram/diffgrams nodes with siblings must have ids, either user specified ones or mapping schema based key field id"
     <Root xmlns:ns00="urnTongue Tiedchemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="All updategram/diffgrams nodes with siblings must have ids, either user specified ones or mapping schema based key field id"?></Root>".

    I have tried a few different posts to resolve this, but with no success.

     

    Here is the schema:

     

    <?xml version="1.0" encoding="utf-16" ?>
    - <xsTongue Tiedchema xmlns:updg="urnTongue Tiedchemas-microsoft-com:xml-updategram" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://sqlteldigticketupdate" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:import schemaLocation="Updategram.xsd" namespace="urnTongue Tiedchemas-microsoft-com:xml-updategram" />
    - <xs:annotation>
    - <xs:appinfo>
    - <references xmlns="http://schemas.microsoft.com/BizTalk/2003">
      <reference targetNamespace="urnTongue Tiedchemas-microsoft-com:xml-updategram" />
      </references>
      </xs:appinfo>
      </xs:annotation>
    - <xs:element name="TicketUpdateIn">
    - <xs:complexType>
    - <xsTongue Tiedequence>
    - <xs:element xmlns:updategram="urnTongue Tiedchemas-microsoft-com:xml-updategram" updategramStick out tonguerefix="updg" minOccurs="1" maxOccurs="unbounded" name="sync">
    - <xs:complexType>
    - <xsTongue Tiedequence>
    - <xs:element updategramStick out tonguerefix="updg" minOccurs="0" maxOccurs="unbounded" name="before">
    - <xs:complexType>
    - <xsTongue Tiedequence>
    - <xs:element minOccurs="0" maxOccurs="unbounded" name="tdgLocateRequests">
    - <xs:complexType>
      <xs:attribute ref="updategram:id" />
      <xs:attribute name="tdgLocateRequestID" type="xs:int" />
      <xs:attribute name="tdgLocateRequestStatusID" type="xs:int" />
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
    - <xs:element updategramStick out tonguerefix="updg" minOccurs="0" maxOccurs="unbounded" name="after">
    - <xs:complexType>
    - <xsTongue Tiedequence>
    - <xs:element minOccurs="0" maxOccurs="unbounded" name="tdgLocateRequests">
    - <xs:complexType>
      <xs:attribute ref="updategram:id" />
      <xs:attribute name="tdgLocateRequestID" type="xs:int" />
      <xs:attribute name="tdgLocateRequestStatusID" type="xs:int" />
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
    - <xs:element name="TicketUpdateResponse">
    - <xs:complexType>
    - <xsTongue Tiedequence>
      <xs:element name="Success" type="xs:anyType" />
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedchema>

     

    Any ideas here?

     

    Thanks

    Dan

    Friday, October 17, 2008 9:05 AM

All replies

  • Can you paste the SQL script for the table, and the input XML you are using?

     

    Sunday, October 19, 2008 5:37 AM
  • Dan,

     

    If you're still seeking an answer, it may be that you're updategram target message (assuming you are mapping from an input message) is missing a key value. In your updategram, the sync-before node should have an element that filters to the record(s) that you want to update and the sync-after node should have this same element and the fields you want to update. Also, if you are updating multiple rows, you'll want to loop the sync node on the source message node that repeats for each update.

     

    If you are still interested in a solution, post a reply and I'll add more detail.

    • Edited by R Sid Thursday, May 1, 2014 3:00 PM
    Thursday, December 4, 2008 2:43 AM
  • Hi

    I am having the same problem.

    Using the message i try to update multiple records in one table.
    The id is a unique identifier in the table.

    <ns0:UpdateRequest xmlns:ns0="http://Eneco.Services.Stroom3.SVO/UpdateRecordStatus/1.0">
     <ns0:sync>
      <ns0:before>
       <ns0:TMPVerzoeken id="07E39C45-19D0-492E-9B78-0DFE3C28EDCC"/>
       <ns0:TMPVerzoeken id="EE3C1306-65E0-45C5-9F9B-793CC726F518"/>
       <ns0:TMPVerzoeken id="BAF55533-7824-4A6A-8A5B-E05FAD9AD205"/>
       <ns0:TMPVerzoeken id="5621ECBB-82F0-43B7-8817-EE69D8B55B42"/>
       <ns0:TMPVerzoeken id="C5E33705-F180-44ED-8C3E-FB690BD8000F"/>
      </ns0:before>
      <ns0:after>
       <ns0:TMPVerzoeken id="07E39C45-19D0-492E-9B78-0DFE3C28EDCC" TMPVerzoek_Status="3"/>
       <ns0:TMPVerzoeken id="EE3C1306-65E0-45C5-9F9B-793CC726F518" TMPVerzoek_Status="3"/>
       <ns0:TMPVerzoeken id="BAF55533-7824-4A6A-8A5B-E05FAD9AD205" TMPVerzoek_Status="3"/>
       <ns0:TMPVerzoeken id="5621ECBB-82F0-43B7-8817-EE69D8B55B42" TMPVerzoek_Status="3"/>
       <ns0:TMPVerzoeken id="C5E33705-F180-44ED-8C3E-FB690BD8000F" TMPVerzoek_Status="3"/>
      </ns0:after>
     </ns0:sync>
    </ns0:UpdateRequest>

    When this message is send to the database the response is:

    The adapter failed to transmit message going to send port "UpdateStatus" with
    URL "SQL://TMP_Verzoeken/". It will be retransmitted after the retry interval specified for this Send Port. Details:"HRESULT="0x80004005" Description="All updategram/diffgrams nodes with siblings must have ids, either user specified ones or mapping schema based key field id"
     ?<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="All updategram/diffgrams nodes with siblings must have ids, either user specified ones or mapping schema based key field id"?></Root>".
    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Thanks for any input

    Kees Wouters
    Friday, February 20, 2009 12:06 PM
  • Try linking the key value from the input message to the key value in the updategram in both the before and after nodes, like this admittedly crude example:

    <InputMessage>                             <TableName> 
    +--<InputRecord>========[loop]===============<sync>--+  
       |                                    <before>--+  
       |                              <TableName>--+  |  
       +--<KeyValue>======+========<keyColumn>--+     |  
       |                   \                 <after>--+  
       |                    \         <TableName>--+  
       |                     +=====<keyColumn>--+  
       |                                        |  
       +--<DataValue>==========<updatedColumn>--+ 

    In the above, the KeyValue from InputMessage is mapped to the before\keyColumn and the after\keyColumn. This produces an effect similar to a SQL statement of Update TableName Set updatedColumn=DataValue Where keyColumn=KeyValue. Mapping the InputRecord to the sync node using a looping functoid will repeat the update for each row of the input message.

    There may be a more efficient method, but this has worked for me in the past. If this answers your question, please mark the post as an answer. If you need more help with your overall solutions, you can contact me directly through the contact me link on the web site shown in my profile.

    Sid
    Friday, February 20, 2009 4:06 PM
  • Sidler, I think your response is clear enough. I also started playing with SQL adpater and need to implementy sync activity between 2 tables in 2 different DBs. So my input is an updategram, my output is also an update gram. I need to do delete, insert and update for syncing my target table. According to your response, I need to map everthing to before and after? am I correct.
    Is ther any tutorial out there?BTS BOL doesnt help so much.
    Regards
    Tuesday, December 22, 2009 6:13 PM
  • Hello Salam,

    I'm not sure I understand the data flow of what you are trying to do, so it is hard for me to say what the mapping would be. If you are not prohibited from using stored procedures, that is the first choice I would use.

    In answer to your question, not everything gets mapped to <before> and <after>. In my above posts, the key value that selects the record to update is mapped to both the <before> and <after> nodes, and the new data value is only mapped to the <after> node. An updategram for updating a single record would look something like:

    <ns0:UpdateRequest xmlns:ns0="http://Eneco.Services.Stroom3.SVO/UpdateRecordStatus/1.0">
    	<ns0:sync>
    		<ns0:before>
    			<ns0:Request id="07E39C45"/>
    		</ns0:before>
    		<ns0:after>
    			<ns0:Request id="07E39C45" Request_Status="3"/>
    		</ns0:after>
    	</ns0:sync>
    </ns0:UpdateRequest>
    

    The above is the equivalent of:
    UPDATE Request SET Request_Status=3 WHERE id='07E39C45'

    To update multiple records, the <sync> node set repeats (not the children of <before> and <after>), like this:

    <ns0:UpdateRequest xmlns:ns0="http://Eneco.Services.Stroom3.SVO/UpdateRecordStatus/1.0">
    	<ns0:sync>
    		<ns0:before>
    			<ns0:Request id="07E39C45"/>
    		</ns0:before>
    		<ns0:after>
    			<ns0:Request id="07E39C45" Request_Status="3"/>
    		</ns0:after>
    	</ns0:sync>
    	<ns0:sync>
    		<ns0:before>
    			<ns0:Request id="EE3C1306"/>
    		</ns0:before>
    		<ns0:after>
    			<ns0:Request id="EE3C1306" Request_Status="3"/>
    		</ns0:after>
    	</ns0:sync>
    	<ns0:sync>
    		<ns0:before>
    			<ns0:Request id="BAF55533"/>
    		</ns0:before>
    		<ns0:after>
    			<ns0:Request id="BAF55533" Request_Status="3"/>
    		</ns0:after>
    	</ns0:sync>
    </ns0:UpdateRequest>
    

    The above is the equivalent of:
    UPDATE Request SET Request_Status=3 WHERE id='07E39C45'
    UPDATE Request SET Request_Status=3 WHERE id='EE3C1306'
    UPDATE Request SET Request_Status=3 WHERE id='BAF55533'

    You can see that updating more than a record or two can quickly become inefficient this way. That is why a stored procedure may be a better alternative.

    As for tutorials, it's been a while since I've searched for them. There is an example in the \Program Files\Microsoft BizTalk Server 2006\SDK\Samples\AdaptersUsage\SQLAdapter folder of the BizTalk installation, which may be helpful.

    I hope this helps,

    • Proposed as answer by R Sid Wednesday, December 23, 2009 12:27 AM
    • Edited by R Sid Thursday, May 1, 2014 3:01 PM
    Tuesday, December 22, 2009 11:41 PM
  • Thanks, this is what I wanted to know. In fact I thought it was a good idea to do an ETLtype of inserting,updating and deleting in one shot which seemscomplicated with updategram
    Sunday, January 3, 2010 6:33 PM
  • Hi,

    You can use a serializable singleton class in .NET for performing all your database operations using ADO.NET. You can route all your database interactions via an object of this class. You can use DLINQ in case you are using .NET 3.5. Inside BizTalk scope you can declare an object of this class and call its methods in an Expression shape. Thus you can perform all Insert, Update, Delete operations. You can also declare a list object in the orchestration to hold the result of select operations. Use implicit transactions of System.Transactions namespace for all your ADO.NET API calls.

    Using so many updategrams would unnecessarily complicate things, you might face deadlock like situations and transaction handling would be poor.


    Please mark as answer if this helps you. Thanks and warm regards Ambar Ray EAI Architect - Microsoft Technologies
    • Proposed as answer by Ambar Ray Thursday, January 7, 2010 12:30 PM
    Thursday, January 7, 2010 12:30 PM