Error on JDBC Receiver Channel

[Former Member]

Hi,

I configured a Receiver JDBC Channel to read data from XI own DB. We are using MS SQL Server 2005.

During the test, I'm running to the following issue. Any ideas on what could be causing this error:

Log for CC monitor:

2010-07-20 13:53:20 Success The message was successfully retrieved from the receive queue. 
2010-07-20 13:53:20 Success Receiver JDBC adapter: processing started; QoS required: ExactlyOnce 
2010-07-20 13:53:20 Success JDBC adapter receiver channel RPI_LOOKUP_JDBC: processing started; party  , service RPI_DB 
2010-07-20 13:53:20 Success Delivering to channel: RPI_LOOKUP_JDBC 
2010-07-20 13:53:20 Success MP: Processing local module localejbs/CallSapAdapter 
2010-07-20 13:53:20 Success MP: Entering module processor 
2010-07-20 13:53:20 Success select sem_datetime, sem_user, sem_host, sem_flag from "/RPI/T_USER" where sem_user=dom1\user1
2010-07-20 13:53:20 Error JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'query' (structure 'stmt'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '/RPI/T_USER'. 
2010-07-20 13:53:20 Error Unable to execute statement for table or stored procedure. 'query' (Structure 'stmt') due to com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '/RPI/T_USER'. 
2010-07-20 13:53:20 Error 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. 'query' (structure 'stmt'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '/RPI/T_USER'. 
2010-07-20 13:53:20 Error Exception caught by adapter framework: null 
2010-07-20 13:53:20 Error 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. 'query' (structure 'stmt'): com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '/RPI/T_USER'.. 
2010-07-20 13:53:20 Error The message status set to NDLV.

Here is the payload:

<root>
 <stmt>
  <query action="SQL_QUERY">
   <access>
    select sem_datetime, sem_user, sem_host, sem_flag from "/RPI/T_USER" where sem_user='dom1\user1'
   </access>
  </query>
 </stmt>
</root>

Connection Settings:

JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection: jdbc:sqlserver://hostname.domain.net;databaseName=XI1;

I did check on the DB and am able to see the table /RPI/T_USER, with 0 records.

Thanks in advance,

Sumant.

[former_member200962]

<root>
 <stmt>
  <query action="SQL_QUERY">
   <access>
    select sem_datetime, sem_user, sem_host, sem_flag from "/RPI/T_USER" where sem_user='dom1\user1'
   </access>
  </query>
 </stmt>
</root>

Not sure if select, from, where will work in lowercase. Instead of query (node under stmt) should you not mention the table_name?

<root>

<stmt>

</RPI/T_USER action="SQL_QUERY">

<access>

SELECT sem_datetime, sem_user, sem_host, sem_flag FROM /RPI/T_USER WHERE sem_user='dom1\user1'

</access>

<//RPI/T_USER>

</stmt>

</root>

I did check on the DB and am able to see the table /RPI/T_USER, with 0 records.

How will the SELECT work then, if the table is with 0 records?

Regards,

Abhishek.

[Former Member]

Hi,

I did some more trouble shooting and found the SQL Server installation to be case-sensitive.

Looks like, the case matters when we specify the table and field names and at the same time, case doesn't matter for the SQL keywords like select, from, where,...

To SAP + MS SQL Server these 2 SQL statements are different (due to case):

1. select sem_datetime, sem_user, from "/RPI/T_USER" where sem_user='dom1\user1'

2. select SEM_DATETIME, SEM_USER from "/RPI/T_USER" where SEM_USER='dom1\user1'

I guess this is a standard installation for SAP and MS SQL Servers based on the collation used.

Regards,

Sumant.

[Former Member]

Hi Sumant, did you try to put the table´s owner before the table name?, or another thing, I don´t if the "/" character works used in the name of a object, let me know if it works.

[Former Member]

Hi Hector,

I am using a 3rd party delivered tool to connect via JDBC. SQL statement is generated by this tool. I don;t have access to modify it.

Thanks,

Sumant.