Problem Description:


The metadata crawl job for sql server database fails with the below exception in the job log.


[INFO] 2019-10-10 05:46:22,258 [main] infoworks.discovery.dbcrawler.rdbms.utils.DBCrawler:188 :: Starting schema crawl for table MSsavedforeignkeyextendedproperties
[WARN] 2019-10-10 05:46:22,312 [main] infoworks.discovery.dbcrawler.rdbms.utils.DBCrawler:657 :: Schema crawl for table MSsavedforeignkeyextendedproperties failed because of following.SQLServerException: The "variant" data type is not supported.
[INFO] 2019-10-10 05:46:22,314 [main] infoworks.discovery.dbcrawler.rdbms.utils.DBCrawler:188 :: Starting schema crawl for table MSsavedforeignkeys
[WARN] 2019-10-10 05:46:22,315 [main] infoworks.discovery.dbcrawler.rdbms.utils.DBCrawler:208 :: Schema crawl for table MSsavedforeignkeys failed because of following.SQLServerException: The connection is closed.


Root cause: 


When the transactional replication configuration is enabled from the SQL server DB end, the System/Metadata tables like MSsavedforeignkeyextendedproperties would be created and Infoworks will try to crawl the metadata for these tables as well.


This system table MSsavedforeignkeyextendedproperties has a column with data type sql_variant and Microsoft JDBC driver does not support sql_variant data type and the above exception would be thrown and the driver will close the connection to the sql server database.


This is a limitation from the SQL Server JDBC driver end but not from Infoworks.See the issue reported here. https://github.com/microsoft/mssql-jdbc/issues/91


Solution:


As a workaround, use the sql server JTDS driver instead of JDBC driver and then crawl the metadata.


a) Login to the edge node with the user who starts Infoworks services.
c) Go to /opt/infoworks/lib/shared.
d) Download the attached JTDS driver from https://sourceforge.net/projects/jtds/files/, unzip it and place the jtds jar in the above location.
e) Change the JDBC connect string in the source to the below format.
jdbc:jtds:sqlserver://<hostname>:1433;database=<db_name>
f) Save the settings.
g) Test the connection and then Re-crawl the metadata.


Applicable Infoworks Versions


IWX v2.4.x,2.5.x,2.6.x,2.7.x,2.8.x