JDBC Receiver error

[former_member188885]

Hi All,

I have a scenario where XI has to pick the file from ftp and update the stagging databse through JDBC.

XI is picking the file, but not getting failed while sending the data to JDBC.

Unable to execute statement for table or stored procedure. 'MSDYNAMICSEMPLINFO' (Structure 'STATEMENT') due to com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MSDYNAMICSEMPLINFO'.

JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'MSDYNAMICSEMPLINFO' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MSDYNAMICSEMPLINFO'

Exception caught by adapter framework: null

MP: Exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'MSDYNAMICSEMPLINFO' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MSDYNAMICSEMPLINFO'

Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'MSDYNAMICSEMPLINFO' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MSDYNAMICSEMPLINFO'

But the client say that the table is created at their end.

Please help

Edited by: Lavanya Ravi on May 17, 2011 8:01 AM

[former_member188885 · Accepted solution]

Can you tell me how to check the structure in external definition?

And one thing is that there are already JDBC scenarios and they are alll working fine. So, if the structure is wrong then the others should also not work right? Do correct me if my understanding is incorrect.

Thanks everyone for your replies

[former_member208856 · Accepted solution]

You have receiver payload, check the structure of that payload with the structures shown in my last post.

Also contact to your JDBC administrator, check if there is any Stored Procedure written on this table, \

which is executing during the time (Immediately) of posting data in that table.

[Former Member · Accepted solution]

Hi,

Go to ESR & under your namespace, rt. click & create external defination.

In external defination select dbtb from the dropdown and select next

select the name of receiver JDBC CC & check out for available tables at DB end.

Thanks,

Krishna

[former_member472138 · Accepted solution]

Hey Lavanya,

Check this out for step by step procedure.

/people/william.li/blog/2008/01/02/sap-pi-71-mapping-enhancements-series-import-sql-tables-metadata

Regards

Pothana

[former_member188885 · Accepted solution]

Hi Sandeep,

I checked for the structure. It looks correct.

[former_member188885 · Accepted solution]

Thank you for your replies Krishna and Pothana.

I am using XI 3.0 and i am not able to find the JDBC communication channel for testing through the external definition. Can you tell me how to check the same in XI 3.0

[Former Member · Accepted solution]

Hi,

Ask your DBA is he able to check the table in DB.

Thanks,

Krishna

[former_member188885 · Accepted solution]

I have checked for the structure and it looks correct.

When they are executing a query in their SQL studio, data is getting updated in their table.

Should anything else be done? Please give your suggestions.

[rajasekhar_reddy14 · Accepted solution]

Hi,

Check your communication channel connection details and SID(Data base name).

if data base name is not correct the there is a chance to get this kind off error.

I think PI debelopment point of view(maping and target structure what you have defined) correct.

Regards,

Raj

[Former Member · Accepted solution]

I agree with Raja, probably there's some error in CC configuration details.

[former_member188885]

Thanks everyone for your replies and suggestions.

The issue is resolved. They have given the database name incorrectly. We found out by accessing the database through SQL Studio.

[Former Member]

Hi,

Do one thing just check mapping and see this particular field that would be mapped with fixed value as procedure name and check weather any extra space etc is there?

regards,

-anuj

[Former Member]

Hai!

U have mentioned that the JDBC target structure is correct.

Then next u want to check whether your Channel is active. If it active then check the Connection details Driver ,Connection details,Username,pwd every think is correct.if all are correct.

In the reciver side check whether the table is defined in any other schema in that database.

For Ex if the data base name is TESTDB and the table name is TESTTABLE then for the lemrnt TABLE in the structure u can give the table name alone. if the table has been defined in anyother schema inside the Database then u want to mention the table as

TESTSCHEMANAEM.TESTTABLE.

Regard's

Preethi.

[former_member188885]

I have tested the mapping seperately and it shows no error.

I tested the ID seperately using the payload in ID and it is working.

But when i execute from the beginning, i am getting the error that i had mentioned.

i also tried to ping the SQL ip from the OS level XI server and the response is received.

Need help to proceed furthur.

[naveen_chichili]

Hi,

Can you please cross check the payload structure by clicking on the message id in JDBC communication channel --> Message content.

Regards,

Naveen

[former_member188885]

In the moni the source has the following structure:

<?xml version="1.0" encoding="utf-8"?>

<ns:MT_MSDYNAMICS_IN xmlns:ns="urn:MSDynamics_integration">

<RECORD>

<ROW>

<EMPLOYEE>200</EMPLOYEE>

<CENTER></CENTER>

</ROW>

<ROW>

<EMPLOYEE>202</EMPLOYEE>

<CENTER></CENTER>

</ROW>

</RECORD>

</ns:MT_MSDYNAMICS_IN>

In the IR, the mapping is as follows:

STATEMENT -> RECORD

DBTABLENAME -> RECORD

ACTION -> CONSTANT( INSERT)

TABLE -> CONSTANT(TABLENAME)

ACCESS -> ROW

FIELDS MAPPED ONE TO ONE.

I have used similar kind of mapping for the other JDBC receiver developments which are working.

Need help in resolving this please

[Former Member]

HI Lavanya,

1) StatementName should be 0..Unbounded and need to be mapped with the source which is repeating which is Record.

2) No need to mapp TableName node.

3) Action will be attribute and needs to be mapped to constant with eiter Insert,Update and UPDATE_INSERT as well.

4) Table will be mapped to the constant and needs to provide the table name in the database.

5) No need to map with access as you have mapped the Record to Statement Name. When ever multiple records exists from source the statement will be excuted for each and every record.

Try to use this mapping and update the status.

Thanks,

Raju

[Former Member]

HI Lavanya,

1) StatementName should be 0..Unbounded and need to be mapped with the source which is repeating which is Record.

2) No need to mapp TableName node.

3) Action will be attribute and needs to be mapped to constant with eiter Insert,Update and UPDATE_INSERT as well.

4) Table will be mapped to the constant and needs to provide the table name in the database.

5) No need to map with access as you have mapped the Record to Statement Name. When ever multiple records exists from source the statement will be excuted for each and every record.

Try to use this mapping and update the status.

Finally if you see the payload in the receiver JDBC channel your structure will be like below. If your payload in the receiver JDBC showing the structure like below then your problem will be solved.

<MessageType Name>

<StatementName>

<dbTableName action=u201DINSERTu201D | u201CINSERTu201D>

<table>MSDYNAMICSEMPLINFO</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</access>

Thanks,

Raju

[former_member188885]

Thank you Narayan.

Can you tell me the format of the connection string?

It is for SQL SERVER 2005

Edited by: Lavanya Ravi on May 24, 2011 10:42 AM

[Former Member]

Dear Lavanya,

The connection string looks like: jdbc:microsoft:sqlserver://XXXXXX:PortNo;DatabaseName=XYZ

Also check if your table is associated with a schema name. In this case it will be like <Table>SchemaName.TableName</Table>

Warm Regards,

Anshul

Edited by: Anshul Chowdhary on May 24, 2011 11:55 AM

[Former Member]

Hi Lavanya

You can check this wiki: http://wiki.sdn.sap.com/wiki/display/VC/JDBCConnectionSetup

URL for SQL SERVER 2005 should be jdbc:sqlserver://<host_name>:<port>;database=<db_name> and driver name should be com.microsoft.sqlserver.jdbc.SQLServerDriver

Regards,

Giuseppe

[Former Member]

Hi Lavanya,

Where ever you are using JDBC as receiver you should follw the below structure which has SAP provided. Your XML structure looks like this.

<StatementName>

<TableName>

<action> (attribute)</action>

<Table>tablename</Table>

<access>

<Filed1> </Filed1>

<Filed2> </Filed2>

<Filed3> </Filed3>

</access>

<key>

</key>

</TableName>

</StatementName>

You have to maintain the structure like. The error which you are getting might be the issue with the structure which you have defined. Change your structure like the above mentioned hope you will be out of issue. Please update your comments if it works.

[Former Member]

Hi,

Did you check the receiver JDBC CC in external defination.

Thanks,

Krishna

[former_member208856]

The problem is due to incorrect structure of receiver JDBC. As you can see the error for parsing data.

Take help for creating correct structure from below link :

http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

[Former Member]

Hi,

Are you using the structure as mentioned in below link:

http://help.sap.com/saphelp_nwpi711/helpdata/en/44/747eda12dd3676e10000000a114a6b/frameset.htm

[Former Member]

Hi Lavanya,

Check out for table name in DB end, 'MSDYNAMICSEMPLINFO'. This table is not available at DB side.

You can also check the availability of the DB table by using external defination in ESR and use your receiver JDBC communication channel to check out for the tables.

If you do not see any table then inform the same to your DBA.

This same problem i also faced a couple of days back, DB tables were not visible through CC.

Thanks,

Krishna

[former_member10771]

HI,

Please go thru this link. This might help you.