#123 'ex.value' breaks from 4.1.1->5.0.0 mssql/jtds

[Darren Hartford]

Hey all,
I admit, this was during testing using SchemaSpyGUI 20090302, however with the only change being from SchemaSpy 4.1.1 to 5.0.0, I'm getting the following issue in a repeated fashion:

==========================
java -jar /home/dhartford/devtools/dbtools/schemaspy/schemaSpyGUI20090302/lib/schemaSpy_5.0.0.jar -dp /home/dhartford/devtools/dbtools/schemaspy/schemaSpyGUI20090302/lib/jtds-driver.jar -t mssql05-jtds -db [mydb] -s dbo -host [myhost] -port 1433 -u [myuser] -o /home/dhartford/devtools/dbtools/schemaspy/schemaSpyGUI20090302/[mytest]

Using database properties:
[/home/dhartford/devtools/dbtools/schemaspy/schemaSpyGUI20090302/lib/schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql05-jtds.properties
Gathering schema details.....................................................................................................................
Failed to retrieve table/view comments: java.sql.SQLException: The multi-part identifier "ex.value" could not be bound.
SELECT OBJECT_NAME(t.object_id) AS TABLE_NAME, ex.value AS comments FROM sys.tables t
(1sec)
===============

I was hoping to leverage the updated Table Comment fix in 5.0.0, but stuck at the moment.

MS SQL 2005 database, jtds driver 1.2.2

[John Currier]

The SQL came indirectly from http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html. Here's the raw SQL:

SELECT OBJECT_NAME(c.object_id) AS TABLE_NAME, c.name AS COLUMN_NAME, CONVERT(varchar(100), 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

Does that SQL fail as well when you directly execute it?

[Brent Palmer]

I am also having the same error with MSSQL 2005. I did run the query you posted in the other comment with no problems. I am using jtds 2.2.5. (At least I thinks so. I put it in <java-home>/lib/)

[Brent Palmer]

Failed to retrieve table/view comments: java.sql.SQLException: The multi-part identifier "ex.value" could not be bound.
SELECT OBJECT_NAME(t.object_id) AS TABLE_NAME, ex.value AS comments FROM sys.tables t

What about using this query instead:
SELECT OBJECT_NAME(c.object_id) AS TABLE_NAME,
CONVERT(varchar(100), ex.value) AS comments
FROM sys.tables c
LEFT OUTER JOIN sys.extended_properties ex
ON ex.major_id = c.object_id AND ex.name =
'MS_Description'
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
ORDER BY OBJECT_NAME(c.object_id)

[John Currier]

Does that SQL work correctly in the .properties file?

John

[Brent Palmer]

I wasn't sure where to look at first. But now I see it:
http://schemaspy.svn.sourceforge.net/viewvc/schemaspy/trunk/src/main/resources/net/sourceforge/schemaspy/dbTypes/mssql05.properties?revision=647&view=markup

I'll try to give it a try sometime in the next few days and report back.

[John Currier]

I've attempted to document it here: http://schemaspy.sourceforge.net/dbtypes.html

[Edward]

John,

I reviewed tickets #128; #97 and this one but I think this is beyond my ability to solve on my own. This was the only open ticket and rather than start new I thought it best to reply here.

After reviewing and applying the suggestions I've seen in these tickets. I am successfully able to have SchemaSpy generate much output for me.

However, I still get this error whenever run (thus no comments). I am using SQL Server 2008 R2; localhost on Windows 7; graphviz-2.38; schemaSpy_5.0.0.jar; and jtds from jtds-1.3.1-dist.zip. Also find attached the mssql05-jtds.properties file being used.

Here is the run command I use to generate my output and then the error:

"C:\Program Files (x86)\Java\jre7\bin\java.exe" -jar "C:\SchemaSpy\schemaSpy.jar" -dp "C:\SchemaSpy\jtds.jar" -t mssql05-jtds -db AdventureWorks2008R2 -host localhost -port 1433 -u xxx -p yyy -connprops "C:\SchemaSpy\properties\mssql05-jtds.properties" -o "C:\SchemaSpy\sa_AdventureWorks2008R2_" -gv C:\GRAPHV~1 -hq -noschema

Using database properties:
[C:\SchemaSpy\schemaSpy.jar]/net/sourceforge/schemaspy/db
Types/mssql05-jtds.properties
Gathering schema details........................................................
................................................................................
................................................................................
................................................................................
................................................................................
.............................................................................
Failed to retrieve table/view comments: java.sql.SQLException: The multi-part id
entifier "ex.value" could not be bound.
SELECT OBJECT_NAME(t.object_id) AS TABLE_NAME, ex.value AS comments FROM sys.tab
les t
(1sec)
Writing/graphing summary..............(13sec)
Writing/diagramming details.....................................................
................................................................................
................................................................................
................................................................................
................................................................................
........................................................................(18sec)
Wrote relationship details of 445 tables/views to directory 'C:\SchemaSpy\ sa_AdventureWorks2008R2_' in 33 seconds.
View the results by opening C:\SchemaSpy\sa_AdventureWorks2008R2_\index.html

C:\SchemaSpy>