posaunenbaer - 2014-05-28

anybody has a solution for creating a SchemaSpy from a SQL Server 2012 with the MS JDBC driver?

I know how to do it with the jTDS JDBC driver:

java -jar ../bin/schemaSpy_5.0.0.jar -dp \
   ../bin/jtds-1.3.1-dist/jtds-1.3.1.jar \
   -t mssql-jtds -connprops testdb.jtds.properties \
   -db ... -s... -host ... -port ... -u ... -p ... -o ...

in the testdb.jtds.properties I added this 2 statements:

selectColumnCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME, C.name AS COLUMN_NAME, EXP.value AS comments \ FROM sys.extended_properties AS EXP \ LEFT OUTER JOIN sys.columns AS C \ ON C.object_id = EXP.major_id \ AND C.column_id = EXP.minor_id \ WHERE \ EXP.class_desc = 'OBJECT_OR_COLUMN' and \ C.name is not null

selectTableCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME, EXP.value AS comments \ FROM sys.extended_properties AS EXP \ LEFT OUTER JOIN sys.columns AS C \ ON C.object_id = EXP.major_id \ AND C.column_id = EXP.minor_id \ WHERE \ EXP.class_desc = 'OBJECT_OR_COLUMN' and \ EXP.minor_id = 0

but when I try it with the JDBC driver provided by Microsoft, than I get this output:

Using database properties:
[../../bin/schemaspy/schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql05.properties
Gathering schema details..................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
Failed to retrieve table/view comments: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not  supported.
SELECT i_s.TABLE_NAME, s.value AS comments FROM INFORMATION_SCHEMA.Tables i_s INNER JOIN sys.extended_properties s ON s.major_id = OBJECT_ID(i_s.table_catalog + '..' + i_s.table_name) WHERE s.class = 1 AND s.name = 'MS_Description'
14:09:43.836 SEVERE:  TDSParser.throwUnexpectedTokenException - ConnectionID:1 ClientConnectionId: aacb73c0-cb9b-4f86-bc77-a9ac1979c444: getNextResult: Encountered unexpected unknown token (0x49)
14:09:43.837 SEVERE:  TDSReader.throwInvalidTDSToken - ConnectionID:1 ClientConnectionId: aacb73c0-cb9b-4f86-bc77-a9ac1979c444 got unexpected value in TDS response at offset:45

Failed to retrieve column comments: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
SELECT OBJECT_NAME(c.object_id) AS TABLE_NAME, c.name AS COLUMN_NAME, ex.value AS comments FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 ORDER BY OBJECT_NAME(c.object_id), c.column_id 
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.ja    va:190)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.checkClosed(SQLServerDatabaseMetaD    ata.java:119)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getImportedKeys(SQLServerDatabaseM    etaData.java:947)
        at net.sourceforge.schemaspy.model.Table.connectForeignKeys(Table.java:106)
        at net.sourceforge.schemaspy.model.Database.connectTables(Database.java:984)
        at net.sourceforge.schemaspy.model.Database.<init>(Database.java:83)
        at net.sourceforge.schemaspy.SchemaAnalyzer.analyze(SchemaAnalyzer.java:211)
        at net.sourceforge.schemaspy.Main.main(Main.java:42)

my command for this is:

java -jar ../bin/schemaspy/schemaSpy_5.0.0.jar \
   -dp ../bin/jdbc-drivers/sqljdbc_4.0.2206.100_enu/enu/sqljdbc4.jar \
   -t mssql05 -connprops testdb.msjdbc.properties \
   -host ... -port ... -db ...-s ... -u ... -p ... -o ...

and the testdb.msjdbc.properties file looks this:

description=MS SQLServer
extends=mssql05

#connectionSpec=jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
connectionSpec=jdbc:sqlserver://<myhostname.domain>\<myinstancename>:1450
host=<myhostname.domain>
port=1450
db=<mydatabasename>

selectColumnCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME, C.name AS COLUMN_NAME, EXP.value AS comments \ FROM sys.extended_properties AS EXP \ LEFT OUTER JOIN sys.columns AS C \ ON C.object_id = EXP.major_id \ AND C.column_id = EXP.minor_id \ WHERE \ EXP.class_desc = 'OBJECT_OR_COLUMN' and \ C.name is not null

selectTableCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME, EXP.value AS comments \ FROM sys.extended_properties AS EXP \ LEFT OUTER JOIN sys.columns AS C \ ON C.object_id = EXP.major_id \ AND C.column_id = EXP.minor_id \ WHERE \ EXP.class_desc = 'OBJECT_OR_COLUMN' and \ EXP.minor_id = 0

Many thanks in advance.

 

Last edit: posaunenbaer 2014-05-28