jtds and SQL Server Named Instances

[Seema Prakash]

hi,

When registering an SQL Server you may specify in the Hostname text the name of the SQL Server and the Instance name in the format \ServerName\InstanceName without a port to enable an Instance connection. Is SQL Server Named Instances supported in jtds 1.2.2 version ?

Thanks

[Khaled AlAkhras]

The correct syntax for named pipes:

url="jdbc:jtds:sqlserver://localhost/dbname;networkProtocol=namedpipes"

[David D. Kilzer]

Yes, add a property to the connection URL called "instance", e.g., "instance=YourInstanceName".

See this page for more details:

http://jtds.sourceforge.net/faq.html#urlFormat

Dave

[Seema Prakash]

Thanks for the reply.

When I tried to connect to my machine locally, it worked.
But when I tried to connect to a remote machine I got an error.

The following are the connection strings that I used.
For local:
jdbc:jtds:sqlserver://localhost/Northwind;instance=sql2005", "sa", "sql2005"

To connect to a remote machine:
jdbc:jtds:sqlserver://dtwxxxxxxx/AgingDatabase;instance=mssqlserver", "ager", "ager"

The error I get is:

Error Code: 90007
Unable to verify whether the specified data connection is available.

For the remote connection when I give the port number along with the instance name does not work if the connect string is

jdbc:jtds:sqlserver://dtwxxxxxxx:2100/AgingDatabase;instance=mssqlserver", "ager", "ager"

But when I change the property name "instance" to "instanceName" and use the connection string as :
jdbc:jtds:sqlserver://dtwxxxxxxx:2100/AgingDatabase;instanceName=mssqlserver", "ager", "ager" it works.

Can you please explain why is this ? Is this the behavior ?

Thanks.

[David D. Kilzer]

As far as I remember, "instance names" only worked locally. (Do they work "remotely" when you connect via the SQL Server database tool? If so, then this is a deficiency/bug in jTDS. Please file a bug about it on the tracker.)

I believe jTDS ignores any properties that it doesn't know about, so adding "instanceName=mssqlserver" is the same as removing "instance=mssqlserver". You can try that easily to check.

Please note that I'm not actively developing jTDS anymore, so my memory is getting kind of fuzzy now. :)

Dave

[Seema Prakash]

Thanks again for the reply.

So, can anyone please confirm that the "instance" property works only when we try to connect to sql server locally?

I will verify the remote connecetion via the SQL Server database tool and let you know.

I see what you are saying about the property other than "instance" will be ignored. So, it connects since it knows the port and the instance name is ignored.

[Seema Prakash]

Another observation that I made was when the port number is not specified and the instance name is given for connecting to the local sql server it connects only if sql server is listening on the default port(1433). If it is listening on a port other than the default port, with no port specified and the instance name specified it fails to connect.

For local connection with no port specified , port that sql server is listening to being other than the default port, and the instance name specified it fails. Here is the connection string:

jdbc:jtds:sqlserver://localhost/Northwind;instance=sql2005", "sa", "sql2005"

Is this intended behavior ?

Thanks,
Seema

[Will Blackmon]

Both local and remote servers work with the instance name parameter. One thing you might want to look into is that if the SQL Server Browser service is running on the server. You will also need to be able to connect to port 1434. This should give you the ability to only supply the instance name parameter and connect even if the port number is different than the default.

Hope this helps,

Will

[BruceS]

Hi Seema,

I believe this port number behaviour is by design...although I don't think that the "instance" property has any effect on the port number issue you are describe.

Jtds specifically connects to port 1433 unless you specify another port number.

Put another way: yes, port 1433 is hard-coded as the default within Jtds. If your SQL Server is not listening on 1433, you need to tell Jtds what port number to connect to.

Regards,
Bruce

[BruceS]

Let me change that, having looked at the code:

If you specify the "instance" property, the "port number" property in the Jtds url is ignored.

If you do not specify the "port" OR the "instance", then 1433 is assumed.

[Seema Prakash]

Thanks for the replies.

SQL Server Browser service is running on my machine that has the named instance. I'm able to see that SQL Server Browser service is listening on UDP port 1434. But I'm still not able to connect without specifying the port number for the named instance if it is not listening on the default port(1433).

[Swarnendu De]

Is it possible to access named instance of sql server remotely through Java
Code using TCP/IP.So far I am able to access default instance remotely but not
named instance.Named instance now I can access only locally through Java
Code.Awaiting Response.

[Bernd Eckenfels]

Yes swarnendu-de, with JTDS you can connect to any instance with TCP bei
eighter using the correct (dynamic) port of the named instance or by using the
instanceName= parameter AND sqlbrowser service:

jdbc:jtds:sqlserver://SERVER:PORT/Database

-or-

jdbc:jtds:sqlserver://SERVER/Database;instanceName=INSTANCE

Note that for a named instance the port is usually not 1433 and for the
default instance ports different that 1433 need to be specified in the URL.

Bernd

[Pretheve]

Hi I tried the seconds option

jdbc:jtds:sqlserver://SERVER/Database;instance=INSTANCE

and still i am not able to connect to the database instance tht i specified in the connection url

please help me