SQL Server foreign key gathering errors using API.

[jseaman-idata]

This is with SchemaCrawler 14.21.02 Using Java JDK 1.8 Windows Server 2012 Scanning a SQL Server DB of version: 13.0.4210 Using the latest SQL Server JDBC Driver

I'm using the API rather than the command line. It works against another SQL Server database (version 12.0.4422.0), Oracle, and mySQL just fine.

Here is the error: Cannot insert the value NULL into column 'name', table 'tempdb.dbo.#foreign_keys_combined_results3e3cce62-1d54-4918-b898-832a905f5014____000000000050'; column does not allow nulls. INSERT fails. Could not retrieve foreign keys for table NOTPROD_PF_WITH_DATALOSS.dbo.student: Cannot insert the value NULL into column 'name', table 'tempdb.dbo.#foreign_keys_combined_results3e3cce62-1d54-4918-b898-832a905f5014____000000000050'; column does not allow nulls. INSERT fails.

Any ideas?

Thanks, Jeff

[sualeh]

SchemaCrawler is read-only, and does not do inserts. You will need to look elsewhere for your answer.

[zhanghuidouble]

@jseaman-idata
the error occurs because of SchemaCrawler loading all schemas of the database，contains system db just like msdb ,tmpdb ,and when SchemaCrawler retrieveForeignKeysFromMetadata, will createForeignKeys with table, but the system table does not support foreign keys， then the error will occur so you can exclude the system db, it also will load faster without system db, just try: final SchemaCrawlerOptions options = new SchemaCrawlerOptions(); options.setSchemaInfoLevel(SchemaInfoLevelBuilder.standard()); options.setRoutineInclusionRule(new ExcludeAll()); options.setSchemaInclusionRule(new RegularExpressionInclusionRule(".abc."))// the schema parttern you need load

[zhanghuidouble]

Thre above method can avoid the error,but the reason is sqlser driver bugs. SchemaCrawler 14.21.02 will download mssql-jdbc 6.4.0 ,but the version of sqlserver has the bugs.see https://github.com/Microsoft/mssql-jdbc/issues/681 After upgrade the version of mssql-jdbc to 7.0.0,has no error.

[sualeh]

@zhanghuidouble - thanks for the information! The next version of SchemaCrawler will have the latest version of the Microsoft JDBC driver bundled in.

[zhanghuidouble]

That's great. We now exclude the version of jar from itself. Look forward to the update ! @sualeh