#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

1 2 > >> (Page 1 of 2)
  • 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!

     
1 2 > >> (Page 1 of 2)