SQL Debatching with Update of data

Asked By Abhishek Iyer

30-Aug-10 01:45 AM

Earn up to 0 extra points for answering this tough question.

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?

re: SQL Debatching with Update of data

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.