#67 add explicit support for sql server instance names

open
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\"

     

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

Sign up for the SourceForge newsletter:





No, thanks