SQL to XI Error The TCP/IP connection to the host has failed

[Former Member]

Dear All

I am doing SQL>XI>BI Integration Scenario. My SQL is 2005 and PI is 7.0

i have installed the drivers sqljdbc.jar at XI server . XI server is able to ping SQL server

Communicateion channel details is

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

Connection is : jdbc:sqlserver://10.10.47.45:1433;DatabaseName=hn_tcs_amit

(10.10.47.45 is the IP of the machine where SQL is installed ,

database name is hn_tcs_amitjain)

Processing details is Qos : Exaclty once

Query SQL Statement : SELECT * FROM t_student WHERE READ_FLAG=0

Update SQL Statement : UPDATE t_student set READ_FLAG=1 WHERE READ_FLAG=0

XI is not picking data from SQL no message in MONI and RWB

while channel monitoring i found the below error with red channel status

Error during database connection to the database URL 'jdbc:sqlserver://10.10.47.45:1433;databaseName=hn_tcs_amitjain' using the JDBC driver 'com.microsoft.sqlserver.jdbc.SQLServerDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:sqlserver://10.10.47.45:1433;databaseName=hn_tcs_amitjain': com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection timed out: connect'

Please help

Regards

priya

[former_member187339 · Accepted solution]

Hi Priya,

Try this

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

Connection : jdbc:sqlserver://<abc>:1433;DatabaseName=<xyz>

We are working here with SQL2005v and have used this paramaeter (check D and N are in caps)

Regards

Suraj

Edited by: S.R.Suraj on Aug 6, 2009 7:14 AM

[Former Member · Accepted solution]

Hi Surja many thanks for the prompt reply

i am very much confused now , whether i have to use this

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://10.10.46.25;user=sa;password=SAPWD@123;database=hn_tcs_amitjain;integratedSecurity=true;

or

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://<abc>:1433;DatabaseName=<xyz>

both are in diffrent format ,

our SQL Server is 2005 Express .

please guide.

Thansk

[Former Member]

issue solved out , telnet servicves should be working at XI system for SQL server ,

1433 ports should be open at Router level.

[Former Member]

Hi Priya,

In order to connect to the Microsoft SQL Server 2005 remotely, TCP/IP needs to be enabled. This is disabled by default on Microsoft SQL Server 2005 Express edition.

Use these steps to enable TCP/IP on Microsoft SQL Server 2005:

From the Microsoft SQL Server 2005 -> Configuration Tools, open the 'Microsoft SQL Server Configuration Manager'.

Expand u2018SQL Server 2005 Network Configurationu2019, and then click u2018Protocols for <Instance Name>u2019.

<Instance Name> is the name of the instance of Microsoft SQL Express being used by the GFI product.

Right-click u2018TCP/IPu2019 and then click u2018Enableu2019. The icon for the protocol will change to show that the protocol is enabled.

And check again.

Regards

Nishant Kumar Singh

[Former Member]

can anyone elp me out

which url to use for SQL 2005 express

among the two which one i have written here above

Thanks

[Former Member]

I have follow this thread for driver name

[Former Member]

Hi,

try to replace Connection parameter in JDBC Adapter as follows:

jdbc: oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ntbomsap13)(PORT = 1527)) ) (CONNECT_DATA = (SID = XI3) (SERVER = DEDICATED) ) )

Format is jdbc:oracle:thin:@hostname:port:SID

Here in this connection* I have replaced: @hostname: port: SID with the TNSNAMES file content.

This should work correctly.

Try

REgards,

Anurag Garg

[former_member187339]

Hi,

Check this blog : /people/charu.agarwal/blog/2009/07/02/problems-faced-while-connecting-to-sql-server-using-jdbc-adapter-in-pi

also in jdbc parameterstry this

jdbc:microsoft:sqlserver://machine name:port;user=;password=;databaseName=XIVERI;SelectMethod=Cursor

Regards

Suraj

[Former Member]

Hi suraj

as per mention the blog above written by charu

i have changed my Data Connection URL as

jdbc:sqlserver://10.10.46.25;user=sa;password=SAPWD@123;database=hn_tcs_amitjain;integratedSecurity=true;

but still the same issue ....

Please help me out .....

Kind Regards

Priya

[former_member187339]

Hi Priya,

Are you able to pin and telnet to the SQL machine from XI system?

Also try puttinh selectMethod=cursor at the end of connection string (as given in my earlier reply)

Regards

Suraj

[Former Member]

Hi Surja

I am able to Ping SQL machine from Xi server

and SelectMethod=Cursor is used for SQL 2000 not for SQL 2005

help me out please