I am sure we all face number of errors while connecting to database using the JDBC adapter in the receiver channel. The motivation of this blog is to highlight series of such errors that one can face while connecting to database, along with their possible solutions.
NOTE: Here the DB used is SQL Server 2005. All the issues and solutions are specific to SQL server.
Usecase- I have configured a JDBC receiver channel in PI 7.1. to import a database table structure in IR by using the new dbtab feature in external definition.
While trying the import of a table in PI 7.1 in the external definition of IR, I faced JDBC connectivity issues in 3 different stages (A, B, C)-
A> The exception during processing the payload.- "Error when calling an adapter by using the communication channel JDBC_Receiver_CH (Party: , Service: BS_SQLServer, Object ID: aaa89203c00d3ff0aee2583ba9e96129) XI AF API call failed. Module exception: (No information available). Cause Exception: 'Error when attempting to get processing resources: com.sap.aii.af.lib.util.concurrent.ResourcePoolException: Unable to create new pooled resource: DriverManagerException: Can not establish connection:: SQLException: [Microsoft][SQLServer JDBC Driver]Error establishing socket.'".
Steps followed-
1) Check whether correct driver is installed or not depending on the version of your SQL server. The jar files for SQLserver 2005 are installed in location com.sap.aii.adapter.lib.sda. To learn more on external driver installation refer to SAP Note Number: 1138877 : PI 7.1 : How to Deploy External Drivers JDBC/JMS Adapters
2) Check whether port is open or not using netstat -na at command prompt. Check for the listening port, entry can be present as "TCP 0.0.0.0:1433 0.0.0.0 LISTENING'"
" Incase the default port 1433 is not registered we need to take the following steps to register the port.
-
Go to SQL server Configuration Manager and enable the TCP ports against the different IP addresses.
"
- Go to -> Network Connections ->Choose "Local Area Connection"-> On right click select "Properties"-> On Local Area Connection properties page select "Advanced Tab-> Settings" -> On windows firewall page select "Advanced Tab -> Settings" -> On services page select "sql remote connections" and "Remote desktop" ->choose "Edit". Now set the hostname and the port number against each of these services and say ‘OK'.
"
- Once the port is registered, you should be able to see the ‘listening port' at netstat -na.
3). Check whether local DB is set for remote access and what is the timeout given for the same.
Go to -> SQL server Management Studio Express ->choose DB instance -> on right click choose "properties" -> select "Connections" from the list.
Now set the remote server connections and timeout parameter.
"
B> Now again check the channel using External definition in IR. May be the database is getting accessed but a problem is encountered while accessing the table structure. For eg:
Error description-
<address>[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (""): Data type 0x38 is unknown.</address>
This issue is related to either the driver installation or URL used in the JDBC receiver channel in configuration directory.
For MS SQL 2000:
Driver Class name: com.microsoft.jdbc.sqlserver.SQLServerDriver
URL: jdbc:microsoft:sqlserver://xiveri-mssql.wdf.sap.corp:1433;user=xiveri;password=sapsap;databaseName=XIVERI;SelectMethod=Cursor
For MS SQL 2005:
Driver Class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://localhost;user=xiveri;password=sapsap;database=AdventureWorks;integratedSecurity=true;
C> Inspite of all the checks above if there are still some problems in accessing your local client system from the PI server, then the firewall of the local system should be turned off.
For eg:
PC firewall ( 
)
If all the above settings are done, then most likely the connectivity between the JDBC adapter on PI server and the SQL server on a remote system should work properly.
