Connecting to MSSQL Server 2000 using JDBC

Hello,

I am developing an application which needs JDBC connection to a Named Instance of SQL Server 2000 (other than default instance). I am facing problem in this regard as my code gives an exception "Time Out" or "Connection Failed". However it works fine with the default named instance...The main driver class is "com.microsoft.sqlserver.jdbc.SQLServerDriver"

and Connection URL is

"jdbc: sqlserver://localhost;instanceName=Testing:1432;databaseName=testDB;"

It gives the exception that Unable to connect to named instance....

Please if any one have any knowledge in this regard....do let me know....I have read an article on MSDN according to which on MS SQL Server 2000 the named Instance other than default can be accessed only through named pipes.

http://msdn2.microsoft.com/en-us/library/aa224779(sql.80).aspx

if some body can tell me Connection string for JDBC driver which utilizes named pipes...the it will be very help full for me.........any help from MSDN Experts is really appreciated...

Thanks

Muhammad:

The JDBC driver does connect to named instances but doesn't use Named Pipes -- only TCP/IP. In your case it looks like you're overloading the instanceName property in the connection string by putting the instanceName and portNumber together. Try breaking them apart like this:

"jdbc: sqlserver://localhost;instanceName=Testing;portNumber=1432;databaseName=testDB;"

If that still doesn't work let me know.

-shelby

|||

Thanks Shelby for your quick reply.....I have tried the string you have given and it is given the same error..

"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

at java.sql.DriverManager.getConnection(DriverManager.java:525)

at java.sql.DriverManager.getConnection(DriverManager.java:193)

at com.frt.test.DatabaseTest.main(DatabaseTest.java:21)"

One thing i want to discuss with you is that when I go to "Server Network utility" the value written in default port for the second instance in "TCP/IP" is 0..why it is so? I have chaged it to 1432 myself and have restarted the server again....My code is as follows (however it is very simple but I am sending it)

import java.sql.*;

public class DatabaseTest
{
public static void main(String[] args)
{
try
{
java.lang.Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver" );
// Class.forName("com.inet.tds.TdsDriver");
Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost;instanceName=TESTING;portNumber=1432;databaseName=testDB;" );
// Connection c = java.sql.DriverManager.getConnection( "jdbcqlserver://localhost/pipe/MSSQL$TESTING/sql/query?database=testDB&user=sa&password=abc");
// Connection c = DriverManager.getConnection("jdbc:inetdae7://localhost/pipe/MSSQL$TESTING/sql/query?database=CMS&user=sa&password=abc");
Statement s = c.createStatement();
ResultSet r =s.executeQuery("select * from table1");
while(r.next())
{
System.out.println(r.getString(2));
}
System.out.println( "Connected!" );
}
catch( Exception ex )
{
ex.printStackTrace();
}
}
}

as you can see that in connection statement I have commented out some lines....that is connection string of some other driver I have downloaded for testing...and when I use it with piped names it connects perfectly to named instance...but I have to use the first driver....so please help me...

Thanks for your coordination

bye

|||

At last i got the solution.....All the problem is due to cause that Service Pack 3.0 for SQL Server 2000 was not installed on my machine...as it is a bug in SQL Server 2000, that it does not listen on TCP/IP port, as u can verify by using TELNET, however this bug has been removed in SP3

Regards

Muhammad Ummar