Skip to Content
avatar image
Former Member

Error in Sender JDBC Communication Channel _SQL database

Hi Experts ,

Scenario : JDBC -> PI-> ABAP Proxy Asynch scenario

XI/PI version:

I am facing problems with the JDBC communication channel in PI. I have a sender JDBC communication channel which is trying to execute a distributed query in a SQL Server 2008 database. My query looks like:

SELECT

* from openquery (PIUS,



'SELECT b.batchid, b.starttime, b.endtime, b.product, b.procedure,



(SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid) AS "unit" FROM piunitbatch b



WHERE b.moduleuid IN (SELECT uid FROM pimoduleh WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''*-30d'' AND b.endtime IS NULL'

)

Query works fine in SQL 2005 or 2008 server studio , but executed the same in JDBC channel throws error in Chanel Monitoring:

Database-level error reported by JDBC driver while
executing statement ''SELECT b.batchid, b.starttime, b.endtime, b.product,
b.procedure, (SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid)
AS "unit" FROM piunitbatch b WHERE b.moduleuid IN (SELECT uid FROM pimoduleh
WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''*-30d'' AND b.endtime
IS NULL')'. The JDBC driver returned the following error message:
'com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'SELECT
b.batchid, b.starttime, b.endtime, b.product, b.procedure, (SELECT m.name FROM
pimodule..pimodule m WHERE uid = b.moduleui'.'. For details, contact your
database server vendor.

I have enabled the option: Database Auto Commit-Enabled

JDBC Driver : com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://PIDKAF.dknz.nzcorp.net;databasename=AutoStand;selectMethod =cursor

tried changing the query and also contacted DB colleague .This query is executed successfully in SQL SErver .

Appreciate your input to sort this issue,

Best regards,

Varalakshmi SB

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Sep 25, 2012 at 08:26 PM

    Try to put the whole SQL statement in SQL studio in one line, then copy and paste it to JDBC sender adapter.

    Be aware that the update statement needs to have exactly the same where clause. Maybe you use a stored procedure for the select.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Experts ,

      Can someone help me with in executing the deleting existing query and reinserting the new entries to SAP in JBDC sender channel .(I have expalined business details in above reply)

      I checkde few blogs for delete query but the SQL query which i have is a open query

      This is my SQL query :

      SELECT * from openquery (PIUS,'SELECT b.batchid, b.starttime, b.endtime, b.product, b.procedure,(SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid) AS "unit" FROM piunitbatch b WHERE b.moduleuid IN (SELECT uid FROM pimoduleh WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''GETDATE()-30'' AND b.endtime IS NULL')