text: Microsoft.com Home
  |
  MSDN Home
  |
  Developer Centers
  |
  MSDN Flash
  |
  Subscribers
  Blogs Home
  |
  Join
  Search
  RSS
  OPML
  Scott Woodgate's OutBursts
  SQL Adapter sample
  previously posted with the labs but highlighting
  here
  because lots of folks keep asking me for it...
  Published Thursday, March 11, 2004 10:20 PM by
  scottwoo
  Colin Lee
  said:
  Thanks. Went through the SQL adapter sample and it was useful as a starting point.
  March 16, 2004 6:44 AM
  Roque
  said:
  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.
  March 18, 2004 8:35 PM
  Masood
  said:
  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
  March 19, 2004 9:35 AM
  Cliff Harker
  said:
  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
  March 19, 2004 1:28 PM
  Cliff Harker
  said:
  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
  March 19, 2004 2:05 PM
  Cliff Harker
  said:
  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
  March 19, 2004 2:08 PM
  Cliff Harker
  said:
  Also in (11)c you say drop into C:\ but that isn't the receive folder location.
  Shouldn't this be
  c:\test\SQLAdapter\Receive
  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
  March 19, 2004 2:20 PM
  Bryant Likes's Blog
  said:
  March 22, 2004 6:49 PM
  Bryant Likes's Blog
  said:
  March 22, 2004 6:59 PM
  David Robinson (Solidsoft Ltd)
  said:
  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.
  March 23, 2004 6:26 AM
  Niklas E
  said:
  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=""><?MSSQLE
  rror HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL Server"
  Description="Could not find stored procedure 'after'."?></OutRootName>".
  <o:p></o:p>
  March 23, 2004 3:23 PM
  MS
  said:
  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
  March 25, 2004 10:38 AM
  Paul
  said:
  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.
  March 28, 2004 5:43 PM
  Scott Woodgate (MS)
  said:
  To the last post select the first receive in the orchestration and go to the property window and choose activate = true.
  March 29, 2004 3:06 AM
  Gary Birch
  said:
  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
  March 29, 2004 12:15 PM
  hung
  said:
  Hi everybody,
  Do you know about default pass thru transmit pipeline and xml transmit pipeline (both of these are for sending).
  That is the difference?
  Some example I work on only work on one pipeline, not the other.
  Same question for default pass thru receive and xml receive pipeline (both of these are for receiving).
  What is the difference? What is the reason to pick one over other in some situation?
  I also have problem with MSMQT (serialization problem).
  I can not get message MSMQ (regular queue) from remote machine to work with MSMQT from a local biztalk machine.
  I can work with them individually, but when they have to send/receive from each other, the serialization format got stuck (I tried every from custom serialization to etc..)
  Basically, I don’t want MSMQ and MSMQT any more, I will use SQL adapter with biztalk 2004.
  My main question here (this question has been asked before by others on some forum but biztalk 2004 is so now, no answer yet):
  I want to save the whole message (XML message from a file receive function).
  The XML message should be able to save as a whole string inside some SQL server table.
  I came a cross some example but only field by field example.
  I need to whole message as a string to work with SQL Adapter for Biztalk.
  Any recommendation?
  Thanks,
  Hung
  March 30, 2004 3:34 PM
  Linus
  said:
  Any ideas about this...
  The adapter "SQL" raised an error message. Details "HRESULT="0x80040e57" Description="String or binary data would be truncated."
  ?<Root xmlns:<?MSSQLError HResult="0x80040e57" Source="Microsoft OLE DB Provider for SQL Server" Description="String or binary data would be truncated."?></Root>".
  March 31, 2004 4:44 AM
  Key file
  said:
  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
  April 1, 2004 12:45 PM
  Swapan K.Das
  said:
  Hi All,
  I am new to BizTalk 2004.Please help.
  I am trying to use SQL Adapter example step 3:
  "Use SQL One-Way Send in Orchestration Scenario."
  to insert into SQL Server Database content of xml file.
  When I drop the xml file into the input folder
  it vanishes immediately but does not insert into SQL table.
  I am getting following error in the "Application Event viewer:"
  1. The Messaging Engine has suspended "1" message(s) from adapter "FILE" due to failures in message processing. Please refer to Health and Activity Tracking tool for more detailed information on this failure.
  2.The "FILE" adapter is suspending a message coming from Source URL:"C:\Tutorial\Lessons\MyImport\InputFile\*xml". Details:"Could not find a matching subscription for the message. ".
  3.The Messaging engine failed to process a message submitted by adapter:FILE Source URL:C:\Tutorial\Lessons\MyImport\InputFile\*xml.
  4.The Messaging Engine encountered an error publishing a batch of "1" messages to the Message Box database for the transport adapter "FILE". Please refer to Health and Activity Tracking tool for more detailed information on this failure and check the endpoint bindings are correctly configured.
  I can execute step 1 "Use SQL Receive in an orchestration" successfully. I am using Windows 2003.
  Please Please help.
  thank you
  regards
  Swapan K.Das
  (swapan@cal2cal.com)
  April 1, 2004 7:16 PM
  Jim Martin
  said:
  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.
  April 5, 2004 5:26 PM
  Nikolai
  said:
  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?
  April 5, 2004 8:20 PM
  JT
  said:
  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
  April 6, 2004 2:07 PM
  JT
  said:
  Please...
  JT
  April 7, 2004 12:21 PM
  Patou
  said:
  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 !!!!!!
  April 7, 2004 2:17 PM
  AV
  said:
  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 !
  April 8, 2004 9:15 AM
  JT
  said:
  Hi again,
  I finally figured out how to install a BizTalk Project into the GAC. (Helps to read the instructions:) ). Now I am stumbling on the Step 10 in getting the filter set properly. The documentation shows the following settings:
  Click OK to close the page
  Send Pipeline: Microsoft.BizTalk.DefaultPipelines.PassThruTransmit
  Filter: BTS.ReceivePortName = ReceivePort_FILE
  In the wizard, I cannot get '=' to come up as one of the possible values in the combo box. Anybody else with this problem?
  John
  April 10, 2004 4:52 PM
  Mike
  said:
  JT
  "==" is what you should select in the combo box
  This is C/C++/C# syntax for "="
  April 13, 2004 11:46 PM
  Ron Fortson
  said:
  I've run into this same issue, but unfortunately haven't found a solution yet.
  Any suggestions?
  Thanks
  April 14, 2004 8:44 AM
  Ron Fortson
  said:
  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.
  April 14, 2004 9:20 AM
  Ram
  said:
  Hi there
  Iam trying to test biztalk server 04 and the file is been picked up and and iam getting this error message.Even i tried Microsoft xml pipe line in send port too.
  Could not find a matching subscription for the message.
  The Messaging engine failed to process a message submitted by adapter:FILE Source URL:C:\Biztalk\FileDrops\*.xml. Details:Could not find a matching subscription for the message.
  Thanks in advance
  Ram
  May 20, 2004 6:22 AM
  Mark
  said:
  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.
  June 9, 2004 6:48 PM
  Terry
  said:
  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?
  July 7, 2004 1:58 AM
  Joe
  said:
  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..
  August 3, 2004 11:40 AM
  Mike Holdorf's Blog
  said:
  February 4, 2005 3:18 PM
  Melbourne BizTalk User Group
  said:
  March 2, 2005 7:29 AM
  SQL Adapter Problem | keyongtech
  said:
  PingBack from
  January 22, 2009 4:46 AM
  New Comments to this post are disabled
  This Blog
  Links
  Syndication
  RSS 2.0
  Atom 1.0
  Recent Posts
  Happy New Year - Virtual PC 2007 Release Candidate ready for download
  Virtual PC 2007 Beta is available - Get going with Windows Vista Today
  VPC free download; Vista EE 4 VM rights; Interested in a Virtual Role?
  New BizTalk Server Blog
  My New Years Resolution And Thanks
  No tags have been created or used yet.
  Archives
  January 2007 (1)
  October 2006 (1)
  July 2006 (1)
  April 2006 (1)
  January 2006 (1)
  November 2005 (3)
  October 2005 (3)
  September 2005 (10)
  August 2005 (4)
  July 2005 (4)
  June 2005 (5)
  May 2005 (6)
  April 2005 (6)
  March 2005 (10)
  February 2005 (8)
  January 2005 (9)
  December 2004 (3)
  November 2004 (7)
  October 2004 (11)
  September 2004 (10)
  August 2004 (15)
  July 2004 (15)
  June 2004 (11)
  May 2004 (12)
  April 2004 (13)
  March 2004 (13)
  February 2004 (7)
  January 2004 (2)
  December 2003 (3)
  November 2003 (6)
  October 2003 (5)
  August 2003 (2)
  Manage Your Profile
  |
  Legal
  |
  |
  MSDN Flash Newsletter
  |
  Trademarks
  |
  Privacy Statement