SQL Adapter sample

re: SQL Adapter sample

Colin Lee — Tue, 16 Mar 2004 13:44:00 GMT

Thanks. Went through the SQL adapter sample and it was useful as a starting point.

re: SQL Adapter sample

Roque — Fri, 19 Mar 2004 03:35:00 GMT

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.

re: SQL Adapter sample

Masood — Fri, 19 Mar 2004 16:35:00 GMT

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

re: SQL Adapter sample

Cliff Harker — Fri, 19 Mar 2004 20:28:00 GMT

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

re: SQL Adapter sample

Cliff Harker — Fri, 19 Mar 2004 21:05:00 GMT

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

re: SQL Adapter sample

Cliff Harker — Fri, 19 Mar 2004 21:08:00 GMT

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

re: SQL Adapter sample

Cliff Harker — Fri, 19 Mar 2004 21:20:00 GMT

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

Already-Initialized Correlation Sets

Bryant Likes's Blog — Tue, 23 Mar 2004 01:49:00 GMT

Already-Initialized Correlation Sets

Bryant Likes's Blog — Tue, 23 Mar 2004 01:59:00 GMT

re: SQL Adapter sample

David Robinson (Solidsoft Ltd) — Tue, 23 Mar 2004 13:26:00 GMT

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.

re: SQL Adapter sample

Niklas E — Tue, 23 Mar 2004 22:23:00 GMT

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>

re: SQL Adapter sample

MS — Thu, 25 Mar 2004 17:38:00 GMT

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

re: SQL Adapter sample

Paul — Mon, 29 Mar 2004 00:43:00 GMT

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.

re: SQL Adapter sample

Scott Woodgate (MS) — Mon, 29 Mar 2004 10:06:00 GMT

To the last post select the first receive in the orchestration and go to the property window and choose activate = true.

re: SQL Adapter sample

Gary Birch — Mon, 29 Mar 2004 19:15:00 GMT

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

re: SQL Adapter sample

hung — Tue, 30 Mar 2004 22:34:00 GMT

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

re: SQL Adapter sample

Linus — Wed, 31 Mar 2004 11:44:00 GMT

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>".

re: SQL Adapter sample

Key file — Thu, 01 Apr 2004 19:45:00 GMT

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

re: SQL Adapter sample

Swapan K.Das — Fri, 02 Apr 2004 02:16:00 GMT

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. Details:Could not find a matching subscription for the message. . This error occurs if the subscribed orchestration schedule or send port has not been started, or if some of the message properties necessary for subscription evaluation have not been promoted. Please refer to Health and Activity Tracking tool for more detailed information on this failure

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)

re: SQL Adapter sample

Jim Martin — Tue, 06 Apr 2004 00:26:00 GMT

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.

re: SQL Adapter sample - XML Binding File Errors

Nikolai — Tue, 06 Apr 2004 03:20:00 GMT

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?

re: SQL Adapter sample

JT — Tue, 06 Apr 2004 21:07:00 GMT

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

re: SQL Adapter sample

JT — Wed, 07 Apr 2004 19:21:00 GMT

Please...
JT

re: SQL Adapter sample

Patou — Wed, 07 Apr 2004 21:17:00 GMT

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 !!!!!!

re: SQL Adapter sample

AV — Thu, 08 Apr 2004 16:15:00 GMT

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 !

re: SQL Adapter sample

JT — Sat, 10 Apr 2004 23:52:00 GMT

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

re: SQL Adapter sample

Mike — Wed, 14 Apr 2004 06:46:00 GMT

JT
"==" is what you should select in the combo box

This is C/C++/C# syntax for "="

re: SQL Adapter sample - XML Binding File Errors

Ron Fortson — Wed, 14 Apr 2004 15:44:00 GMT

I've run into this same issue, but unfortunately haven't found a solution yet.

Any suggestions?

Thanks

re: SQL Adapter sample - XML Binding File Errors

Ron Fortson — Wed, 14 Apr 2004 16:20:00 GMT

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.

re: SQL Adapter sample

Ram — Thu, 20 May 2004 13:22:00 GMT

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

re: SQL Adapter sample

Mark — Thu, 10 Jun 2004 01:48:00 GMT

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.

re: SQL Adapter sample

Terry — Wed, 07 Jul 2004 08:58:00 GMT

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?

re: SQL Adapter sample

Joe — Tue, 03 Aug 2004 18:40:00 GMT

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..

re: SQL Adapter Errors - Revisited

Mike Holdorf's Blog — Fri, 04 Feb 2005 23:18:00 GMT

Link to SQL Adapter Samples

Melbourne BizTalk User Group — Wed, 02 Mar 2005 15:29:00 GMT

SQL Adapter Problem | keyongtech

SQL Adapter Problem | keyongtech — Thu, 22 Jan 2009 12:46:09 GMT

PingBack from http://www.keyongtech.com/324197-sql-adapter-problem