add explicit support for sql server instance names
Brought to you by:
johncurrier
Menu
▾
▴
#67 add explicit support for sql server instance names
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
http://randomactsofcoding.blogspot.com/2009/01/database-documentation-using-schemaspy.html see section "How to use SchemaSpy to Diagram a Named Instance"
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
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.
don't know if it matters but I'm using jtds-1.2.5.jar
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
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>\<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.
Use -connprops=instanceName\=myInstance
I'm assuming that instanceName is what they call the property. Note that = is escaped with a \.
John
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!
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
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.
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
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 :-)
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
ok, great. i'll try it when i get a chance at work (you won't catch me running mssql at home!)
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\"