jtds and SQL Server Named Instances

2009-03-19
2014-06-06
  • Seema Prakash

    Seema Prakash - 2009-03-19

    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

      Khaled AlAkhras - 2009-04-10

      The correct syntax for named pipes:

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

       
    • David D. Kilzer

      David D. Kilzer - 2009-03-19

      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

      Seema Prakash - 2009-03-19

      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

        David D. Kilzer - 2009-03-19

        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

      Seema Prakash - 2009-03-20

      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

      Seema Prakash - 2009-03-20

      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

        Will Blackmon - 2009-03-21

        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

        BruceS - 2009-03-21

        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

          BruceS - 2009-03-21

          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

      Seema Prakash - 2009-03-24

      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

    Swarnendu De - 2010-06-30

    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

    Bernd Eckenfels - 2010-07-05

    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

      Pretheve - 2014-06-06

      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

       
      Last edit: Pretheve 2014-06-06

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks