'sys_id' is specified more than once in the SET when loading cmdb_ci

[SamoSlaus]

Here is the error: `C:\ServiceNowDatamartLoader\sndml-3.2.0>java -Xmx1048M -jar sndml-3.0.2-mssql.jar -p profile.txt -y cmdb_ci_full.yml 15:00:24 INFO [main] Globals GLOBAL INIT: loadProperties C:\ServiceNowDatamartLoader\sndml-3.2.0\profile.txt 15:00:24 INFO [main] Session GLOBAL INIT: instance= 15:00:24 INFO [main] Database GLOBAL INIT: database=;databaseName= 15:00:25 INFO [main] Generator GLOBAL INIT: dialect=mssql schema=null namecase=LOWER namequotes=SQUARE 15:00:25 INFO [main] LoaderConfig GLOBAL INIT: metrics: cmdb_ci.metrics tables:

• {name: cmdb_ci, action: insert, truncate: true, partition: week} 15:00:25 INFO [main] Database cmdb_ci INIT: truncate table cmdb_ci 15:00:25 INFO [main] Database cmdb_ci PROCESS: truncate table cmdb_ci 15:00:26 WARN [main] ColumnDefinitions cmdb_ci.schema SCHEMA: domain type=12 size=40 (not mapped) 15:00:30 INFO [main] RestTableAPI cmdb_ci PROCESS: getStats count=975837 query="sys_created_on<2020-10-07 13:00:25" createdRange=[2014-11-09 14:51:24,2020-10-07 11:14:27] 15:00:30 INFO [main] LoaderJob cmdb_ci INIT: partition=DatePartition[interval=WEEK size=309 min=2014-11-06 max=2020-10-08] 15:00:30 INFO [main] LoaderJob cmdb_ci INIT: begin load cmdb_ci (975837 rows) 15:00:30 INFO [main] RestTableAPI cmdb_ci.W2020-10-01 PROCESS: getStats count=4375 query="sys_created_on>=2020-10-01^sys_created_on<2020-10-07 13:00:25^ORDERBYsys_created_on^ORDERBYsys_id" Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: The column name 'sys_id' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1794) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315) at servicenow.datamart.DatabaseInsertStatement.insert(DatabaseInsertStatement.java:39) at servicenow.datamart.DatabaseInsertWriter.writeRecord(DatabaseInsertWriter.java:34) at servicenow.datamart.DatabaseTableWriter.processRecords(DatabaseTableWriter.java:47) at servicenow.api.RestTableReader.call(RestTableReader.java:71) at servicenow.api.RestTableReader.call(RestTableReader.java:6) at servicenow.api.DatePartitionedTableReader.call(DatePartitionedTableReader.java:142) at servicenow.api.DatePartitionedTableReader.call(DatePartitionedTableReader.java:15) at servicenow.datamart.LoaderJob.call(LoaderJob.java:172) at servicenow.datamart.Loader.loadTables(Loader.java:95) at servicenow.datamart.Loader.main(Loader.java:60)`

[gflewis]

This looks like the 3.0.2 JAR. Can you try running with the 3.2.0 JAR, and see if you get the same error?

If that does not work, can you try dropping the cmdb_ci table, and then run again, and send the log. I would like to see what the CREATE TABLE looks like.

Thanks Giles

[SamoSlaus]

Found out that datamart.schema is now mandatory. Added it and it's working! Thanks!