I am using the jTDS driver in a Java application. The database administrator told me that the SQL Server instance name is
MSSQL-DB09v1\v1
How should I write the connection URL?
I have been using something like
jdbc:jtds:sqlserver://server-name/database_name
for some time and it works well, but don't know the right connection string when the instance name contains a backslash.
Did further research and tests. Found out the correct connection URL string in this case is:
jdbc:jtds:sqlserver://server-name/database_name;instance=instance_name
In my case, the connection string is:
jdbc:jtds:sqlserver://server-name/MSSQL-DB09v1;instance=v1
I had a similar case where my DBA gave me a database on a server with the following connection: {SERVER_NAME}\{INSTANCE}. That syntax worked when connecting to the server and instance using SSMS, but it didn't work when connecting through the Java driver.
Instead, the following syntax worked for me:
jdbc:jtds:sqlserver://{SERVER_NAME};databaseName={DATABASE_NAME);instance={INSTANCE}
Notice that I had to move the instance to a connection string parameter. Once I did this, everything worked well.
For reference, see this specific JTDS FAQ.
-Your database would be running on a "dbo" schema.
-So you need to make sure you establish a connection to felicitate querying in a way the dbo schema handles your query.(While you are using getConnection() function)
-You can do that by adding name of your database in front of the server name in the connection string. like below
ConnectionUrl="jdbc:jtds:sqlserver://**yourservername:1433/yourdatabase**;database=yourdatabse;user=username;password=password;encrypt=false;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
instead of
ConnectionUrl="jdbc:jtds:sqlserver://**yourservername:1433**;database=yourdatabse;user=username;password=password;encrypt=false;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;";
