Share via

SQL Adapter sample

  • Article

previously posted with the labs but highlighting here because lots of folks keep asking me for it...

Comments

  • Anonymous
    March 15, 2004
    Thanks. Went through the SQL adapter sample and it was useful as a starting point.
  • Anonymous
    March 18, 2004
    When I was trying your SQL Two-Way Send in Orchestration Scenario sample, there's an error when I want to create SQL send por. The error is "Generated instance document does not comply with schema supplied. There was an error generating the XML document. There was an error generating the XML document."
    How can I solve this.
    Thanks in advance.
  • Anonymous
    March 19, 2004

    It was very Useful. However I am getting Error when I Build SQL Two-Way Send in Orchestration Scenario sample. My error is Use of Unconstructed Message Message_2

    Message_2 Is assigned to Send Shape and connected to SQL2Way Send Request Port.

    What am I miising,

    Any help would be Appreciated

    Thanks
    Masood
  • Anonymous
    March 19, 2004
    Had a play with the SQL example and worked my way through.

    Stumbled at the first deploy (3g) where I had to create a key file (sn -k) before it would work.

    Also stuck on the second part where I'm getting an error on the SendPort_SQL saying "Transmission failure".

    Also a couple of bits need padding out for the total novice. (5) Create Ports and Bindings doesn't tell you to run Biztalk Explorer which threw me for a mo'.

    Also (11) Test. This says generate a document without telling you how. Also the doc created was nothing like your example and in fact all fields were strings (no dates or currency).

    Cliff
  • Anonymous
    March 19, 2004
    Feedback on my feedback!

    In the 2nd example where you create the XSD at the start for some reason all 3 fields are created as strings for me and this messes up the document instance later.

    Why does the XSD generation not generate "datetime" and "float" fields?

    Cliff
  • Anonymous
    March 19, 2004
    Also your example shows

    <ns0:InRootName....

    and a single record

    and what is generated is

    <ns0:ReqRoot....

    and 3 blocks of 3 records, all the same

    Which should it be?

    Sorry to be picky but something isn't working and I'm grabbing at straws!

    Cliff

  • Anonymous
    March 19, 2004
    Also in (11)c you say drop into C: but that isn't the receive folder location.

    Shouldn't this be

    c:testSQLAdapterReceive

    So I've tweaked the XSD and done everything over again and dropped the XML into the correct directory and it disappears almost immediately and I still get

    Transmission Failure, SendPort_SQL, URL = SQL://Biztalk2004/Northwind

    on the PassThroughTransmit

    I tried integrated security and using "sa" but still fails.

    Stuck!

    Cliff
  • Anonymous
    March 22, 2004
    I got this working, but was wondering how you would handle a stored procedure parameter that takes ntext, how would you pass a whole xml document string to the stored procedure.

    Obviously if the stored procedure just needed a couple of elements from the xml document then you could do a mapping from the source schema to the sql generated schema. Not sure if you could map a whole document (including tags etc) into a string element of another schema.
  • Anonymous
    March 23, 2004
    We have a SQL UpdateGram schema that used to work fine in earlier biztalk
    builds, but after installing RTM we get this error. It seems like the
    adapter tries to run an extra stored procedure, even if the schema specifies an
    updategram. If I check what is run in SQL
    Profiler, then the first record works fine, but it continues with an extra
    non-desirable exec 'after', which is totally wrong of course and then it
    roll-backs and stops. I am updating many products and I have tried with:
    a.. Many sync-blocks with one after-block in each sync-block. (this is
    what I want and that I used in EAP beta 4421 without problems)
    b.. One sync-block with many after-blocks in the sync-block (this is not
    want I want since each sync is one transaction and I don't want everything
    to roll-back if one product is wrong)
    If I try with only one product it works, but I'd like to insert all products
    and according to the SQLXML documentation both many sync-blocks and many
    after-blocks is valid.

    Since the beta expires on 31st of March 2004, a bug-fix for this on the RTM
    would be nice before that.

    Attached is a sample with 3 products only.

    This is the error:

    The adapter "SQL" raised an error message. Details "HRESULT="0x80040e14"
    Description="Could not find stored procedure 'after'."<o:p></o:p>

    ?<?xml version="1.0" encoding="utf-16" ?><OutRootName
    xmlns="http://Metsa.Integration.Schemas.SQL.LocalProductDataUpdate"><?MSSQLE
    rror HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server"
    Description="Could not find stored procedure 'after'."?></OutRootName>".

    <o:p></o:p>
  • Anonymous
    March 25, 2004

    In using SQL 2 WAY Send with orchestration. When I drop the instance file with CustomerID ='HANAR' In Receive Folder.I get the same File in Send Folder.
    Should It not return all the orders for CustomerID='HANAR' in the SEND Folder? or is there something that I am missing?

    MS
  • Anonymous
    March 28, 2004
    Please Help:
    When trying to compile as specified in 4G, I got an error.

    Error: Orchestration.odx(170): you must specify at least one already-initialized correlation set for a non-activation receive that is on a non-selfcorrelating port

    What did I do wrong? Anybody tried this before?

    Thanks.
  • Anonymous
    March 28, 2004
    To the last post select the first receive in the orchestration and go to the property window and choose activate = true.
  • Anonymous
    March 29, 2004
    If you want to use a SP to insert a record and return the PK(@@Identity) value, what is the process. I am new to BTS.

    Thanks in advance
  • Anonymous
    March 30, 2004
    The comment has been removed
  • Anonymous
    March 30, 2004
    Any ideas about this...
    The adapter "SQL" raised an error message. Details "HRESULT="0x80040e57" Description="String or binary data would be truncated."
    ?<Root xmlns:ns00="urn:schemas-microsoft-com:xml-updategram"><?MSSQLError HResult="0x80040e57" Source="Microsoft OLE DB Provider for SQL Server" Description="String or binary data would be truncated."?></Root>".
  • Anonymous
    April 01, 2004
    HI,
    Regarding deploy of SQL Adapter sample... Understand need to create a key file prior to deploy. Where does the key file need to be placed? I assume in the solution directory. Does the solution or the project need to reference the .snk in anyway, or is physical co-location sufficient? Thanks.
    JT
  • Anonymous
    April 01, 2004
    The comment has been removed
  • Anonymous
    April 05, 2004
    Swapan, I believe the reason you are getting the "Could not fine a matching subscription for the message" is because on your send port you did not set up a filter describing what messages need to be sent via that particular send port.
  • Anonymous
    April 05, 2004
    I am trying to use a binding file to deploy my SQL Adapter send/receive ports and I get the following errors in attempting to import the binding files:

    "Failed updating binding information. BindingException: Could not validate TransportTypeData or Address properties for Primary Transport of Send Port 'SendReceivePort_FS_PO_SQLUpdate'. Exception from HRESULT: 0x80131500"

    Has anyone come across the same issues?
  • Anonymous
    April 06, 2004
    I am back to beg for mercy here. I am a real novice, and cannot get past step 3g - deploying the adapter sample. I have generated a .snk, but do not know how to proceed to associate the key with the assembly so that I can deploy to GAC. PLEASE HELP!
    JT
  • Anonymous
    April 07, 2004
    Please...
    JT
  • Anonymous
    April 07, 2004
    Swapan,

    Did you fix your error? I'm having the same error. File -> Orch -> File.

    It has to do with either namespaces, ports, or a stupid setting somewhere, but I can't figure it.

    We have a super dummy example and that one works !!!!!!
  • Anonymous
    April 08, 2004
    I can not complete the 'Create Ports and Bindings' section ( see chapter 5).
    I deployed the solution but the 'SQL receivePort' mentioned in the documents is not in 'receive ports' folder.
    Becouse of this, I can not create the receive location, as described in section.

    I am using Biz2004 without the rollup pack on Ms Server 2003.

    Where am i wrong ? Does anybody help me?
    Thanks and happy Easter !
  • Anonymous
    April 10, 2004
    The comment has been removed
  • Anonymous
    April 13, 2004
    The comment has been removed
  • Anonymous
    April 14, 2004
    I've run into this same issue, but unfortunately haven't found a solution yet.

    Any suggestions?

    Thanks
  • Anonymous
    April 14, 2004
    I knew that once I posted this I'd find a solution. When I exported the bindings, the connection string had all asterisks in the password. I edited the binding file to change the password to the actual value and it worked.


  • Anonymous
    May 19, 2004
    The comment has been removed
  • Anonymous
    June 09, 2004
    What about Hierarchical inserts? Say you have an "Orders" table with a related "Order Details" table... that could have 1 or many related records to it's parent, the "Orders" table.
  • Anonymous
    July 06, 2004
    With reference to thread from Mark (SQL Adapter sample 6/9/2004 6:48 PM) on Hierarchical Inserts. Does any anyone have any infromation on this, it must be a common scenario.
    When using the SQL adapter to insert the parent (order record), isnt it possible to get hold of the @@IDENTITY just inserted in the response message?
  • Anonymous
    August 03, 2004
    Like your second feedback on the blog I am getting the exact same error.

    "Generated instance document does not comply with schema supplied. There was an error generating the XML document. There was an error generating the XML document."

    This is simply when I try to define a request-response port configuration for the SQL adapter. Any help would be appreciated, it happens when I try and configure the connection information, namespace, and root element. I am using the correct values for each I triple checked them..
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/324197-sql-adapter-problem