#67 add explicit support for sql server instance names

open
John Currier
5
2010-08-20
2010-08-19
Tim
No

add an option to the command line parameters for instance name, or make it clear how to set an instance name.
"instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made."
jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
http://msdn.microsoft.com/en-us/library/ms378428%28SQL.90%29.aspx

thx

Discussion

  • John Currier
    John Currier
    2010-08-19

    For SQL Server -host maps to everything between // and :portNumber. That means that all you should have to do to specify an instance is use:

    -host myServerName\myInstanceName

    John

     
  • John Currier
    John Currier
    2010-08-19

    • status: open --> pending
     
  • Tim
    Tim
    2010-08-20

    Thanks john but I'm afraid that doesn't work for me.

    java -jar "C:\Program Files\SchemaSpy\schemaSpy_5.0.0.jar" -t mssql05-jtds -host dbserver\myinstance -port 1433 -s dbo -db mydb
    Using database properties:
    [C:\Program Files\SchemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql05-jtds.properties
    Failed to connect to database URL [jdbc:jtds:sqlserver://dbservermyinstance:1433/mydb]
    java.sql.SQLException: Unknown server host name 'dbservermyinstance'.

    And again but quoting and escape the hostname:

    java -jar "C:\Program Files\SchemaSpy\schemaSpy_5.0.0.jar" -t mssql05-jtds -host "dbserver\\myinstance" -port 1433 -s dbo -db mydb ...
    Using database properties:
    [C:\Program Files\SchemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql05-jtds.properties
    Failed to connect to database URL [jdbc:jtds:sqlserver://dbserver\myinstance:1433/mydb]
    java.sql.SQLException: Unknown server host name 'dbserver\myinstance'.

    ----

    Even if this did work I think it would be handy to have a specific parameter to make it clear what to do.

     
  • Tim
    Tim
    2010-08-20

    • status: pending --> open
     
  • Tim
    Tim
    2010-08-20

    don't know if it matters but I'm using jtds-1.2.5.jar

     
  • John Currier
    John Currier
    2010-08-20

    Every JDBC driver has its own way of dealing with the connection string. The Microsoft page you referenced is for the Microsoft drivers, not JTDS. For the page to have any chance of working you'd have to be using -t mssql-05 with the Microsoft drivers.

    If you want to use the JTDS driver then look at their connection spec docs: http://jtds.sourceforge.net/faq.html#urlFormat

    Hope this helps,
    John

     
  • Tim
    Tim
    2010-08-23

    Ah, good point. <grin> Thanks for the link to the jtds docs.

    I can't use the mssql-05 driver due to lack of support for variant so I have to use the jtds driver.

    The jtds doc (below) says the instance name has to be a separate property in the url. Is there any way to get schema-spy to put that extra property into the url?

    instance
    Named instance to connect to. SQL Server can run multiple so-called "named instances" (i.e. different server instances, running on different TCP ports) on the same machine. When using Microsoft tools, selecting one of these instances is made by using "<host_name>\&lt;instance_name>" instead of the usual "<host_name>". With jTDS you will have to split the two and use the instance name as a property.

     
  • John Currier
    John Currier
    2010-08-23

    Use -connprops=instanceName\=myInstance

    I'm assuming that instanceName is what they call the property. Note that = is escaped with a \.

    John

     
  • Tim
    Tim
    2010-08-24

    Woo, sorted. Thanks John.

    java -jar "C:\Program Files\SchemaSpy\schemaSpy_5.0.0.jar" -t mssql05-jtds -host myhost -port 1433 -connprops=instance\=myinstance -db mydb -s dbo -o output -u sa -pfp -noimplied -dp "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar" -gv "C:\Program Files\Graphviz2.26.3\"

    is what I've finally settled on.

    It would still be great for usability of the tool if did get an explicit instance option, but this works great for me now. Thanks again!

     
  • John Currier
    John Currier
    2010-08-27

    You could create an extension of mssql05-jtds that modifies the connectionSpec to include instance. It'd just need a line like "extends=mssql05-jtds" to inherit everything from from mssql05-jtds along with the modified connecitonSpec and a instance= help line.

    Then you'd point to where ever you store it (mssql05-jtds-instance.properties??) with -t and specify your instance with -instance. The MS SQL configuration files are already a rat's nest. I really think that adding instance-based versions of each (and doubling the number of MS SQL configurations as a result) would just make it worse.

    John

     
  • Tim
    Tim
    2010-08-27

    okay. would it need two versions of the jtds properties file or would it be possible to have the instance option defined as optional in the main jtds file?

    I'm not so fussed personally now as I have something that works for me, but would like to see it easier for anyone coming to this from fresh.

     
  • John Currier
    John Currier
    2010-08-29

    My concern is that there already are two versions of jtds properties files (one normal and one for 05). It's not a huge deal, but there'd be four jtds properties files. Fortunately I allow property file inheritance so it wouldn't be a copy/paste maintenance nightmare.

    For naming would we just slap "-instance" on the end of the name?

    John

     
  • Tim
    Tim
    2010-08-29

    I agree it would be better not to end up with four properties files!

    I think from a user perspective the most important thing is that when trying to set up schemaspy for the first time and encountering a named instance, that there is something in the documentation or command line parameters that makes it clear what to do.

    "-instance" sounds ok to me. Would certainly be a good clue to a user :-)

     
  • John Currier
    John Currier
    2010-08-30

    I've added two .properties files to the beta: mssql-jtds-instance.properties and mssql05-jtds-instance.properties. Can you try it with -t mssql05-jtds-instance to make sure it works before I commit them?

    Thanks,
    John

     
  • Tim
    Tim
    2010-08-30

    ok, great. i'll try it when i get a chance at work (you won't catch me running mssql at home!)

     
  • Tim
    Tim
    2010-08-31

    downloaded the beta http://schemaspy.sourceforge.net/schemaSpy.jar
    and tried it out.

    works a treat. good work!

    (i renamed the jar for convenience)

    java -jar "C:\Program Files\SchemaSpy\schemaSpy-beta.jar" -t mssql05-jtds-instance -host dbserver -port 1433 -instance=myinstance
    -db mydb -s dbo -o output -u sa -pfp -noimplied -dp "C:\Program Files\jtds-1.2.5\jtds-1.2.5.jar" -gv "C:\Program Files\Graphviz2.26.3\"