PI JDBC Commmunication Channel Error

[Former Member]

Hello Experts,

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 2000 database. My query looks like:

select * from [ServerName].[DatabaseName].[dbo].[ViewName]

The reason I have to specify the Server Name and Database Name is because I am connecting to a different database on a different server through PI and then trying to execute the above query. All the required database permissions have been set up correctly and this query works fine if I execute it on SQL Server Management Studio. But in RWB in channel monitoring I am seeing these errors.

Database-level error reported by JDBC driver while executing statement 'select * from [ServerName].[DatabaseName].[dbo].[Viewname] where role_id is not null'. The JDBC driver returned the following error message: 'java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.'. For details, contact your database server vendor.

This looks like a database error but I wanted to check if there could be a problem with JDBC driver used by PI.

Any help, ideas will be greatly appreciated.

Thanks,

Abhijit

[baskar_gopalakrishnan2 · Accepted solution]

The error you get from database. YOu dont need to worry. Talk to DBA team. Just curious..What PI version do you use? SInce Jdbc version relying on jdk version and PI too. If you use PI version 7.1 then you are running JDK 5 and supposedly you have to install jdbc drivers that support JDK 5. Just few cents.

[Former Member · Accepted solution]

Thanks Baskar. Yes, we are using PI 7.1. The issue is only with this particular distributed query that I am using. Normal queries work fine. Currently I am having a hard time convincing our DBA's that its a database issue and not PI.

[baskar_gopalakrishnan2 · Accepted solution]

Please try to test using toad or some db client tool this query and check whether you see any error.

[Former Member · Accepted solution]

Hi Baskar,

I can execute the query successfully on SQL Server Management Studio. So there is nothing wrong with the query. We are exploring some more options on the database side to handle this error.

Abhijit

[Former Member · Accepted solution]

Database Auto-Commit-Enabled option had to be checked in communication channel for this to work. The issue has been resolved now. Thanks Baskar for looking into this issue.

Abhijit

Edited by: Abhijit Ghag on Oct 21, 2011 8:29 PM