- This topic has 11 replies, 1 voice, and was last updated 6 years, 10 months ago by
.
-
Posts
-
-
The SQL Receive adapter is a polling adapter. You set the schedule in the adapter – basically a polling interval. If this interval is 30 minutes the adapter will poll the database every 30 minutes, execute the query and return the resultant Xml message to the MessageBox.
If your orchestration is bound to the SQL receive port an orcheatration instance will be initiated on receipt of this message.
There is no special configuration in the orchestration for a SQL receive port it is the same as using a File receive adapter. -
You need to add the schema to your project.
1. manually create the schema to match the SQL output – not for the faint hearted.2. Let Biztalk/SQL create the schema for you – the preferred method.
Project -> Add -> Add Generated Items…
Add Adapter Metadata -> Add
Select SQL -> Next
Set connection string -> Next
Specify the target namespace ( a unique string)
Select Receive Port
Specify root node -> NextSelect \”Select statement\” -> Next
Enter your select statement, which should end in FOR XML -> Next
Finish
-
1. and 2. in previous reply are options not steps.
Option 2 Add Adapter Metadata is the preferred option.
-
Try FOR XML AUTO or FOR XML AUTO, ELEMENTS if you want the output in elements rather than attributes
-
I think the Xml you are sending to the SQL adapter is being interpreted as a stored procedure call rather than an updategram.
Can you email me a copy of the message and the updategram schema.
-
Hi,
I have this situation:
the database db1 is filled with all orders. when this database (or table) is changed he must be copied to the database db2 so the application that works on db2 must have the correct databasevalues.in bizTalk i must add a SQL port who receives the changes. but i have heard that the SQL adapter has a polling scheduler.
then my question ishow does this work? how do i configure my BizTalk orchestration so this can work?
Thx in advance
Thomas-
o i see,
but what i don’t understand, i must configure my receive port in biztalk orchestration to receive a message. this message i create must be of a xml schema. but how do i know how this schema will look like if i don’t have an example of the receiving message of the sql adapter?
Any explenation on that?
thx
Thomas-
-
yeah one more question
i put in this select statement:
SELECT ID, ORDER_ID, PRODUCTNAME FROM Articles FOR XML
but it doesn’t work :s (in my sql editor it does) if i delete for xml still doesn’t work…
thx for the help
-
thx that works.
but something doesn’t…
when add an send port also, and i also generate the send port message (it is to update another database with the same values)
i do everything ok, i map the two messages, i have in my send message this structure:
[quote:7e17e1b748]<sync>
<before>
<tablename>
<columns></columns>
</tablename>
</before>
<after>
<tablename>
<columns></columns>
</tablename>
</after>
</sync>[/quote:7e17e1b748]in my before and after i have all the columnnames.
so what do i do,
i connect the \”ID\” from the receive message to the \”ID\” from the BEFORE block of the send message.
i connect the \”Aantal\” from the receive message to the \”Aantal\” from the AFTER block of the send message.i build it, i deploy it, it all works.
but i start my host instance and then the only thing i’m getting is errors in my event log.this is my error message:
[quote:7e17e1b748]A message sent to adapter \”SQL\” on send port \”SendSQLOrderSysteem\” with URI \”SQL://AWS00401/Ordersysteem/\” is suspended. Error Details: HRESULT=\”0x80040e14\” Description=\”The request for procedure ‘Artikelen’ failed because ‘Artikelen’ is a table object.\”[/quote:7e17e1b748]The funny thing about it is i don’t use a procedure, i have a sql statement in my receive port and have used the updatagram on my send port…
How could i solve this?
Thx in advance
Thomas-
hi thx for your help but i allready did some changes.
now i have this.
database A has table: Artikelen
–> ID, Order_ID, Productnaam, Aantal, StatusDatabase B has table: ArtikelenMagazijn
–> ID, Order_ID, Productnaam, Aantalnow what i did is adding a status, so only the statusses with 1 would be updatet.
I made a stored procedure in the SQL Server 2005:
[quote:6ecbaaab3e]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[SP_ArtikelenSelect]
@Status varchar(1)
AS
select ID, Order_ID, Productnaam, Aantal, Status
from Artikelen
where Status = @Status
for xml auto, XMLDATA
[/quote:6ecbaaab3e]then i made a generated item again for the receive port with the stored procedure, where i put the value of status to \”1\”.
next i add a generated item again for the send port with all the database fields of database B. (artikelenMagazijn)
next i have a transformmap where i connect ID from receiveschema to the ID in the <BEFORE> from the SEND schema.
and i connect the Aantal from RECEIVE schema to the Aantal in the <AFTER> from the SEND Schema.Now this works again not like it has to be…
normally the status from database A should change to 0 so he wouldn’t be approached like every second.
and i have this error in my eventlog:
[quote:6ecbaaab3e]A message sent to adapter \”SQL\” on send port \”SendSQLOrderSysteem\” with URI \”SQL://AWS00401\\STAGE/Ordersysteem/\” is suspended.
Error 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> [/quote:6ecbaaab3e]any luck to solve this?
thx for everything
Thomas -
Greg,
I am having problems with the SQL adapter where it crashes in the middle of the wizard with no error. IThe wizard is at the point where I believe I would enter the SQL statement.
I go through the wizard to the point of Database Information –> Schema Information –> Statement Type Information, here I click next and it drops, no warning, no error. I have seen a couple of solutions that don’t work: 1. data type not supported – not an issue, I have 1 variable and have changed to severa supported data types. 2. roll back to BT 2004 to create the SQL adapter and migrate forward – not an option, we are starting new with BT 2006 Evaluation version.
I am looking at this third option of creating the SQL adapter manually, which you reference here. Do you have instuctions on this, or some references on how to do this?
I would really, really appreciate any help!
Thank you!
(I’m a newbie!)[quote=\”greg.forsythe\”]You need to add the schema to your project.
1. manually create the schema to match the SQL output – not for the faint hearted.2. Let Biztalk/SQL create the schema for you – the preferred method.
Project -> Add -> Add Generated Items…
Add Adapter Metadata -> Add
Select SQL -> Next
Set connection string -> Next
Specify the target namespace ( a unique string)
Select Receive Port
Specify root node -> NextSelect \”Select statement\” -> Next
Enter your select statement, which should end in FOR XML -> Next
Finish[/quote]
-
-
-
-
-
-
-
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.