How to send multiple custome tables from SAP ECC to MS SQL tables in single interface..?

[Former Member]

HI ,

Please help me on this

sender : SAP ECC

Receiver : MS SQL

I need to send multiple cutom tables which were created in ECC to MS SQL in single interface .

please susgest

Regards,

Vinod

[Former Member · Accepted solution]

Hello,

U mean, u want to read data from multiple tables from ECC and then insert/update table in SQL?

If yes, then i would suggest you to create proxy in ECC and read data from tables and send the same to PI.  For SQL server, u can either create SP in DB (recommended) and then send data in below format to DB:

<StatementName5> 

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName > 

  </StatementName5>

OR U can directly use Update_Insert action to send entries to DB

  <StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

  </StatementName1>

Search SDN u will find lot of blogs on the same.

Document Formats for the Receiver JDBC Adapter (SAP Library - Partner Connectivity Kit)

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

Thanks for your reply

in my case I have 11 different tables existed in ECC and for these 11 tables fields getting data from different standard and custom tables existed in ECC.

now requirement is, I need to send these 11 tables from ECC to PI and PI will insert the data into 11 tables which were created in MS SQL.

please suggest

regards,

Vinod

[Former Member · Accepted solution]

For get to mention it has to be single INTERFACE..

[Former Member · Accepted solution]

Hello,

As already stated, for ECC side u can use proxy and send data to PI - I don't think there could be any other better option than this.

Now, for DB u can choose both the option like u can create 11 (update_insert or insert) statements in PI for those 11 tables and do ur mapping (i hope all those 11 tables belong to same DB?).

U can check below blog for this approach ( i would suggest you to go through all the series part 1 to 6)

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/05/jdbc-receiver-best-practices-scen...

Or the other way is to create SP in DB and let SP take care of performing DML operations in DB. This way u will have better flexibility in doing error handling and performance wise also it's a good solution (if i were u then i would have opted this).

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Yes it belongs to single DB.

Can you please explain about key field concept and how can i implement in JDBC receiver structure as am thinking to use UPDATE_INSERT statement..?

Is it mandatory to give key field tag for each statement..?

[Former Member · Accepted solution]

Hello,

>>Can you please explain about key field concept and how can i implement in JDBC receiver structure as am thinking to use UPDATE_INSERT statement..?

From SAP Help: UPDATE_INSERT:

The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

So for update_insert operation, at runtime PI will first try to execute statement as update operation using a key field value and if in case that record do not exist in DB then that particular statement will work as a Insert statement. So u have to create structure in below format where cardinality of StatementName should be 1..Unbounded.

So suppose there are 10 records present in a source side (coming from ECC) then u have to map target structure in such a way that 10 "StatementName" nodes are generated.

<root>

  <StatementName1>

<dbTableName action=”UPDATE” | “UPDATE_INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

  </StatementName1>

<root>

>>Is it mandatory to give key field tag for each statement..?

Yes, for update key tag is mandatory.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Thanks Amit..:)

please tell me how can i select the key fields where I have 10 fields under access see below ex:

               access

                    vinod

                    company

                    age

                    sex

                    state

                    address

                    GFNmae

                    GFHieght

                    GFCompany

               key

                    ??

                    ??

please tell me how to fill the above key tag ..?

[Former Member · Accepted solution]

and one more what about key1 & key2   ? please

[Former Member · Accepted solution]

Hello,

>>please tell me how can i select the key fields where I have 10 fields under access see below ex:

U cannot choose it on ur own, for each table u have to check from ur DB team that what are the Primary/Key fields maintained.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit GM

Thanks for your inputs. plz tell me how to achieve below requirements in reciver JDBC .

En  1. Enclose all SQL queries in a transaction. This way, if there is an error on the SQL side, there will be a rollback which will prevent having partial updates.

2. Capture the return code from the SQL query in case of error and throw it back to the PI alert monitoring. The complete SQL error with description.

Please suggest how to get above two points

[Former Member · Accepted solution]

Hello,

>>Enclose all SQL queries in a transaction. This way, if there is an error on the SQL side, there will be a rollback which will prevent having partial updates.

By default transaction level will be with PI, which means that even one of the query statement in ur XML message (containing multiple DML statements) gets failed in DB then PI will automatically rollback all the previous statements executed in the SP/Table.

In JDBC receiver, go to advanced tab -> Select Advanced Mode -> and Select Transaction Isolation Level -> Serializable (if u want u can choose default setting also)

>>Capture the return code from the SQL query in case of error and throw it back to the PI alert monitoring. The complete SQL error with description.

Just enable alert mechanism in ur system and SQL errors (which will occurred while performing DML operations) caught by JDBC channel will be reflected in Alert Mails/Inbox

BTW, don't merge multiple questions in a single thread. So if want to ask new question then u have to raise a new thread for that - be acquainted  with Rules of engagement.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Sure i will raise new threads for new questions.Thanks a lot Amit for your support

[Former Member · Accepted solution]

Good, so if ur question is answered then close this thread.

[iaki_vila]

HI Vinod,

The best way is the way mentioned by Amit. Also, if you have a DB developer team you can choose another option, they can do a stored procedure to receive all the data and to do internally the table actualization. Another option is the DB team will develop a SOAP interface in the DB, it's not too difficult and you will can connect with Proxy-SOAP interface, the SOAP interface is wider and in my opinion more generic if in the future th DB is going to upgrade or to change.

Regards.