BizTalk - SQL Debatching with Update of data

Asked By Abhishek Iyer
30-Aug-10 01:45 AM

Can a horde of SQL entries be debatched at a single time and all of them updated according to the schema created from the stored procedure.

 

I know this happens for single row retrieved values. Just wanted to know whether they can be done for batches. Also, if the destination schema namespace is different from the source's, can this be achieved. That's because i saw a blog and the person used a single schema namespace at both ends.

My case is SQL-> SAP scenario.

Also, can you suggest a link/links that gives a good information about WCF-SAP and WCF-SQL adapters?

  Mohan Raj Aryal replied to Abhishek Iyer
30-Aug-10 02:01 PM
I can see two options of debatching your message in your case: 

1) Debatching Logic in SQL itself: 
You can write SQL SELECT query in such a way that it retrieves small batches of message using record/s based on some condition. For e.g. If you have 10 records you can execute SELECT query with 10 times using query like: 

DECLARE @id = SELECT TOP 1 ID FROM YOURTABLE AND STATUS<>'PROCESSED'
SELECT * FROM YOURTABLE WHERE ID=@id
UPDATE YOURTABLE SET STATUS='PROCESSED' WHERE ID=@id

2) Debatching in Orchestration: 
Another alternative is to debatch your message in Orchestration using xpath. You will need to find the count of records in the message and using loop shape you can constract small batches of message and inside Loop shape. Inside loop shape you can construct small message using xpath and send it to SAP system using send port. For details you can refer this sample.
Create New Account
help
Biztalk Stored Procedure problems with NULL value Hi All, In my Biztalk(2004) Orchestration, I use a stored procedure to update some tables in a database hosted by SQL Server 2003. The input of
Calling Stored procedure without parameters in BizTalk using SQL Adapter Hi I know how to use a SQL adapter calling a stored procedure with parameters from Orchestration, but how to call stored procedure without parameters when I generate metadata for the stored procedure I m just gettin the request root node so my question is do I
Calling parameterized SQL Stored Procedure through Orchestration Windows Server I want to call stored procedure with simple SELECT query [in loop] from orchestration which takes an input parameter [say customerCode am not aware of the way wherein I can pass the variable parameter to my stored procedure. I call the stored procedure in loop [say 5 times] and each time I call the SP I need to
Error message from SQL adapter Windows Server I have a two-way send-receive SQL BizTalk port (BizTalk 2006). This port executes a stored procedure (SQL Server 2005). This stored procedure returns the content of a column of type 'text'. Sending a request to the sql port triggers the following error from BizTalk: A message sent to adapter "SQL" on send port "RequestToSQLPort" with URI "SQL: / / localhost / MyDB suspended. Error details: HRESULT = "0x 7a" Description = "No error description provided" After having edited the stored procedure so that the text column is converted to a local varchar(8000) variable and
xml DATA FROM ORCHESTRATION to stored procedure xml parameter Windows Server i want to pass xml DATA FROM ORCHESTRATION to stored procedure xml parameter. I want to copy xml tree from source message to Stored procedure xml parameter. My problem is that mass copy cannot be assigned to a attribute field While generated scheama wizard makes parameters of stored procedure as an attribute. So how can i copy the xml into a generated schema xml