InvalidDateTimeException when sys_created_on is empty

After an import from a legacy system, we're finding some records have no value for sys_created_on, which causes exceptions in both sndml2 and sndml3.

SNDML3 exception

Exception in thread "main" servicenow.core.InvalidDateTimeException:
        at servicenow.core.DateTime.<init>(DateTime.java:76)
        at servicenow.core.DateTime.<init>(DateTime.java:57)
        at servicenow.rest.RestTableAPI.getStats(RestTableAPI.java:60)
        at servicenow.rest.MultiDatePartReader.initialize(MultiDatePartReader.java:79)
        at servicenow.datamart.TableLoader.call(TableLoader.java:129)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

Below is an example SOAP response from debug logging on SNDML2 (not sure how to do debug logging in sndml3 yet; my log4j settings file isn't working)

<sys_class_path>/!!/!$/#^</sys_class_path>
<sys_created_by>SalesForce</sys_created_by>
<sys_created_on />
<sys_domain>f4d05ca0db58434041d8d7795e9619ca</sys_domain>
<sys_domain_path>!!!/!!)/!!#/!!$/</sys_domain_path>
<sys_id>343263c4dba85b00a43689584b9619b1</sys_id>

Configuration

Launch command

java -jar -Dlog4j.configuration=file:/home/user/sndml3/log4j.debug sndml-3.0.1-b.4-mssql.jar -p properties.prpty -y tables.yaml

properties.prpty

servicenow.instance=https://*****.service-now.com/
servicenow.username=*****
servicenow.password=*****
servicenow.limit=200
servicenow.getkeys_limit=20000
datamart.url=jdbc:sqlserver://*****:1433;database=ServiceNow
datamart.username=*****
datamart.password=*****
datamart.dialect=mssql
datamart.schema=*****
datamart.check_readable=false

tables.yaml

metrics: checkpoint_allscripts.txt

tables:
- {source: cmdb_ci, truncate: true, partition: month}
- {source: cmdb_ci_service, truncate: true, partition: month}

log4j.debug (doesn't seem to work in v3 the way it did in v2)

log4j.rootLogger=DEBUG, Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{HH:mm:ss} [%t] %p %c %m%n
# This is what I used in v2. Just started with v3 this morning; haven't dug into how logging is different yet.

Wow! That's a new one. It looks like the empty date is crashing the partition logic.

Can you try splitting it as follows so that it does not try to partition on a null date.

metrics: checkpoint_allscripts.txt
tables:
- {source: cmdb_ci, truncate: true, partition: month, filter: "sys_created_onISNOTEMPTY"}
- {name: cmdb_ci_null, source: cmdb_ci, filter: "sys_created_onISEMPTY"}
- {source: cmdb_ci_service, truncate: true, partition: month, filter: "sys_created_onISNOTEMPTY"}
- {name: cmdb_ci_service_null, source: cmdb_ci_service, filter: "sys_created_onISEMPTY"}

This may not completely solve the problem. It may just cause it to crash in a different place. If so, I can try to patch the code in the next release.

SNDML3 uses Log4j2. The property name is different (log4j.configurationFile) and the config file format is also different. I added a page to the wiki.

Ran through the split config - looks like it just crashed in a different place. Also, thanks for the debug info.

[...]
15:15:01 INFO [main] TableInsertWriter cmdb_ci:M2009-12-01 PROCESS: loaded 200 (69295 / 69313)
15:15:02 INFO [main] TableInsertWriter cmdb_ci:M2009-12-01 PROCESS: loaded 218 (69313 / 69313)
15:15:02 INFO [main] TableLoader cmdb_ci:M2009-12-01 TERM: end load cmdb_ci (69313 rows)
15:15:03 INFO [main] RestTableAPI cmdb_ci_null: PROCESS: getStats query=sys_created_onISEMPTY count=4
15:15:03 INFO [main] TableLoader cmdb_ci_null: INIT: begin load cmdb_ci_null (4 rows)
15:15:04 INFO [main] TableUpdateWriter cmdb_ci_null: PROCESS: loaded 4 / 4
15:15:04 INFO [main] TableLoader cmdb_ci_null: TERM: end load cmdb_ci_null (4 rows)
15:15:04 INFO [main] Database : INIT: truncate table allscripts.cmdb_ci_service
15:15:04 INFO [main] Database : PROCESS: truncate table allscripts.cmdb_ci_service
Exception in thread "main" servicenow.core.InvalidDateTimeException:
        at servicenow.core.DateTime.<init>(DateTime.java:76)
        at servicenow.core.DateTime.<init>(DateTime.java:57)
        at servicenow.rest.RestTableAPI.getStats(RestTableAPI.java:60)
        at servicenow.rest.MultiDatePartReader.initialize(MultiDatePartReader.java:79)
        at servicenow.datamart.TableLoader.call(TableLoader.java:129)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

I'm having trouble reproducing this. Can you please retest with the 3.0.1-b.6 jar and provide fresh stack dump. Thanks.

If it helps to reproduce, I'm 99% sure the data were loaded through import sets & this link indicates the autoSysFields flag can be used to override the created date. (I haven't tried to reproduce the initial data condition in a dev environment yet)

I reran the job with 3.0.1-b.6 (and /src/main/resources/log4j2.xml)

Log settings

Modifications from the stock file:

:%s/info/debug/g - Switch from info to debug

:%s/>>/>/g - removed an extra > on line 16

<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
<Appenders>
<Console name="Console">
  <ThresholdFilter level="debug" />
  <PatternLayout>
    <Pattern>%d{HH:mm:ss} %level [%thread] %logger{1} %X{context} %marker: %msg%n</Pattern>
  </PatternLayout>
</Console>
</Appenders>
<Loggers>
<Root level="debug">
  <AppenderRef ref="Console" />
</Root>
<Logger name="org.apache.http" level="debug" />
<Logger name="servicenow.common" level="debug" />
</Loggers>
</Configuration>

Output

It looks like HTTP response body is being logged in its gzip'd format, not plain text.

[[Modified to change session IDs and domain names]]
18:27:25 INFO [main] Database cmdb_ci_service INIT: truncate table mydomain.cmdb_ci_service
18:27:25 INFO [main] Database cmdb_ci_service PROCESS: truncate table mydomain.cmdb_ci_service
18:27:25 DEBUG [main] PartSumTableReader cmdb_ci_service INIT: initialize query="sys_created_onISNOTEMPTY"
18:27:25 DEBUG [main] JsonRequest cmdb_ci_service REQUEST: GET https://mydomain.service-now.com/api/now/stats/cmdb_ci_service?sysparm_query=sys_created_onISNOTEMPTY&sysparm_count=true&sysparm_min_fields=sys_created_on&sysparm_max_fields=sys_created_on
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : CookieSpec selected: best-match
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 0][name: BIGipServerpool_mydomain][value: 444618762.52542.0000][domain: mydomain.service-now.com][path: /][expiry: null] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 0][name: JSESSIONID][value: 531B8EDE][domain: mydomain.service-now.com][path: /][expiry: null] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 0][name: glide_session_store][value: 0ECB1924][domain: mydomain.service-now.com][path: /][expiry: Mon Feb 26 18:28:25 UTC 2018] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 1][name: glide_user][value: "U0N2Mjb2lycThiWWkvZUlSN0NiTA=="][domain: mydomain.service-now.com][path: /][expiry: Sat Mar 16 21:24:18 UTC 2086] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 0][name: glide_user_route][value: glide.f1e62e204e][domain: mydomain.service-now.com][path: /][expiry: Sat Mar 16 21:24:17 UTC 2086] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAddCookies cmdb_ci_service : Cookie [version: 1][name: glide_user_session][value: U0N2Mjb2lycThiWWkvZUlSN0NiTA==][domain: mydomain.service-now.com][path: /][expiry: null] match [(secure)mydomain.service-now.com:443/api/now/stats/cmdb_ci_service]
18:27:25 DEBUG [main] RequestAuthCache cmdb_ci_service : Auth cache not set in the context
18:27:25 DEBUG [main] PoolingHttpClientConnectionManager cmdb_ci_service : Connection request: [route: {s}->https://mydomain.service-now.com:443][total kept alive: 1; route allocated: 1 of 2; total allocated: 1 of 20]
18:27:25 DEBUG [main] PoolingHttpClientConnectionManager cmdb_ci_service : Connection leased: [id: 0][route: {s}->https://mydomain.service-now.com:443][total kept alive: 0; route allocated: 1 of 2; total allocated: 1 of 20]
18:27:25 DEBUG [main] MainClientExec cmdb_ci_service : Stale connection check
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "[read] I/O error: Read timed out"
18:27:25 DEBUG [main] MainClientExec cmdb_ci_service : Executing request GET /api/now/stats/cmdb_ci_service?sysparm_query=sys_created_onISNOTEMPTY&sysparm_count=true&sysparm_min_fields=sys_created_on&sysparm_max_fields=sys_created_on HTTP/1.1
18:27:25 DEBUG [main] MainClientExec cmdb_ci_service : Target auth state: UNCHALLENGED
18:27:25 DEBUG [main] MainClientExec cmdb_ci_service : Proxy auth state: UNCHALLENGED
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> GET /api/now/stats/cmdb_ci_service?sysparm_query=sys_created_onISNOTEMPTY&sysparm_count=true&sysparm_min_fields=sys_created_on&sysparm_max_fields=sys_created_on HTTP/1.1
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Accept: application/json
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Host: mydomain.service-now.com
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Connection: Keep-Alive
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> User-Agent: Apache-HttpClient/4.3.6 (java 1.5)
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Cookie: BIGipServerpool_mydomain=444618762.52542.0000; JSESSIONID=531B8EDE; glide_session_store=0ECB1924; glide_user="U0N2Mjb2lycThiWWkvZUlSN0NiTA=="; glide_user_route=glide.f1e62e204e; glide_user_session="U0N2Mjb2lycThiWWkvZUlSN0NiTA=="
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Cookie2: $Version=1
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 >> Accept-Encoding: gzip,deflate
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "GET /api/now/stats/cmdb_ci_service?sysparm_query=sys_created_onISNOTEMPTY&sysparm_count=true&sysparm_min_fields=sys_created_on&sysparm_max_fields=sys_created_on HTTP/1.1[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Accept: application/json[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Host: mydomain.service-now.com[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Connection: Keep-Alive[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "User-Agent: Apache-HttpClient/4.3.6 (java 1.5)[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Cookie: BIGipServerpool_mydomain=444618762.52542.0000; JSESSIONID=531B8EDE; glide_session_store=0ECB1924; glide_user="U0N2Mjb2lycThiWWkvZUlSN0NiTA=="; glide_user_route=glide.f1e62e204e; glide_user_session="U0N2Mjb2lycThiWWkvZUlSN0NiTA=="[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Cookie2: $Version=1[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "Accept-Encoding: gzip,deflate[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 >> "[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "HTTP/1.1 200 OK[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Content-Encoding: gzip[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "X-Is-Logged-In: true[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Set-Cookie: glide_session_store=0ECB1924;Secure; Expires=Mon, 26-Feb-2018 18:28:25 GMT; Path=/; HttpOnly[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Pragma: no-store,no-cache[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Cache-control: no-cache,no-store,must-revalidate,max-age=-1[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Expires: 0[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Content-Type: application/json;charset=UTF-8[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Transfer-Encoding: chunked[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Date: Mon, 26 Feb 2018 18:27:25 GMT[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Server: ServiceNow[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "Strict-Transport-Security: max-age=63072000; includeSubDomains[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "a[\r][\n]"
18:27:25 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "[0x1f][0x8b][0x8][0x0][0x0][0x0][0x0][0x0][0x0][0x0][\r][\n]"
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << HTTP/1.1 200 OK
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Content-Encoding: gzip
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << X-Is-Logged-In: true
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Set-Cookie: glide_session_store=0ECB1924;Secure; Expires=Mon, 26-Feb-2018 18:28:25 GMT; Path=/; HttpOnly
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Pragma: no-store,no-cache
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Cache-control: no-cache,no-store,must-revalidate,max-age=-1
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Expires: 0
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Content-Type: application/json;charset=UTF-8
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Transfer-Encoding: chunked
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Date: Mon, 26 Feb 2018 18:27:25 GMT
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Server: ServiceNow
18:27:25 DEBUG [main] headers cmdb_ci_service : http-outgoing-0 << Strict-Transport-Security: max-age=63072000; includeSubDomains
18:27:25 DEBUG [main] MainClientExec cmdb_ci_service : Connection can be kept alive indefinitely
18:27:25 DEBUG [main] ResponseProcessCookies cmdb_ci_service : Cookie accepted [glide_session_store="0ECB1924", version:0, domain:mydomain.service-now.com, path:/, expiry:Mon Feb 26 18:28:25 UTC 2018]
18:27:26 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "45[\r][\n]"
18:27:26 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "[0xab]V*J-.[0xcd])Q[0xb2][0xaa]V*.I,)[0x6]1[0x92][0xf3]K[0xf3][0x80]"J[0x6]J:J[0xb9][0x89][0x15]`[0xb9][0xca][0xe2][0xf8][0xe4][0xa2][0xd4][0xc4][0x92][0xd4][0x94][0xf8][0xfc]<[0xa0][0x9c]R-P.3[0xf][0x87][0x1c][0x10][0x0][0x0][0x90][0xc3][0x8e][0x9c]Z[0x0][0x0][0x0][\r][\n]"
18:27:26 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "0[\r][\n]"
18:27:26 DEBUG [main] wire cmdb_ci_service : http-outgoing-0 << "[\r][\n]"
18:27:26 DEBUG [main] PoolingHttpClientConnectionManager cmdb_ci_service : Connection [id: 0][route: {s}->https://mydomain.service-now.com:443] can be kept alive indefinitely
18:27:26 DEBUG [main] PoolingHttpClientConnectionManager cmdb_ci_service : Connection released: [id: 0][route: {s}->https://mydomain.service-now.com:443][total kept alive: 1; route allocated: 1 of 2; total allocated: 1 of 20]
18:27:26 DEBUG [main] JsonRequest cmdb_ci_service RESPONSE: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=90
Exception in thread "main" servicenow.api.InvalidDateTimeException:
        at servicenow.api.DateTime.<init>(DateTime.java:76)
        at servicenow.api.DateTime.<init>(DateTime.java:57)
        at servicenow.api.RestTableAPI.getStats(RestTableAPI.java:48)
        at servicenow.api.PartSumTableReader.initialize(PartSumTableReader.java:83)
        at servicenow.datamart.TableLoader.call(TableLoader.java:127)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

A possible fix to consider:

This should (untested) default any empty date string to 1970-01-01 - Though, without the plain-text response body, I'm still just guessing this'll fix things.

diff --git a/src/main/java/servicenow/core/DateTime.java b/src/main/java/servicenow/core/DateTime.java
index 8521aea..3f6cdd3 100644
--- a/src/main/java/servicenow/core/DateTime.java
+++ b/src/main/java/servicenow/core/DateTime.java
@@ -15,7 +15,8 @@ public class DateTime implements Comparable<DateTime> {

        public static final int DATE_ONLY = 10; // length of yyyy-MM-dd
        public static final int DATE_TIME = 19; // length of yyyy-MM-dd HH:mm:ss
-
+       public static final int DATE_EMPTY=  0; // length of ''
+
        public static final int HOURS_PER_DAY = 24;
        public static final int MIN_PER_DAY = 60 * HOURS_PER_DAY;
        public static final int SEC_PER_DAY = 60 * MIN_PER_DAY;
@@ -64,6 +65,7 @@ public class DateTime implements Comparable<DateTime> {
         * @throws InvalidDateTimeException
         */
        public DateTime(String value, int fmtlen) throws InvalidDateTimeException {
+               if (fmtlen == DATE_EMPTY) {value = "1970-01-01"; fmtlen = DATE_ONLY;} //set default for empty string
                DateFormat df;
                switch (fmtlen) {
                case DATE_ONLY : // 10

There are better ways to implement the same outcome; but I went for minimally invasive - added 2 lines of code.

It is blowing up in RestTableAPI.getStats (line 48). This code should be modified to check for a null or empty string before calling the DateTime constructor.

The thing I cannot figure out is why that method is being called at all. It is called from PartSumTableReader.initialize. The PartSumTableReader class should only be used if the partition option is set. Otherwise the TableLoader should create a RestTableReader which should call RestTableAPI.getStats with includeDates=false, in which case line 48 should never get executed.

As a workaround, see if you can load that table with partition not set.

In any case, I will try to patch RestTableAPI tonight, and we can see if that helps.

p.s. I was able to use sysAutoFields to create a record with a null sys_created_on, but when I ran the loader it did not blow up, so I must have been doing something wrong.

If you put <Logger name="servicenow" level="trace" /> in your log4j2.xml file you should get a dump of the input and output. I have been trying to use the following system:

INFO - Summary info when a group of records is processed
DEBUG - One line for each Web Services call and/or record processed
TRACE - Full dump of the JSON or XML

The package servicenow.common was renamed to servicenow.api.

I found the org.apache.http debugging not particularly helpful unless you are trying to see HTTP headers and the ilk.

I have released a beta 7 which has some improvements in the logging. Try using either log4j2-test.xml or log4j2-debug.xml from src/test/resources as a starting point for your log4j config. Please also specify -ea (enable assertions) on the Java command line.

Can you let me know what version of ServiceNow you are running? I am starting to think that the reason I cannot reproduce your issue is that the behavior of the REST "stats" API has changed with regards to the handling of null values. It may be based on the version of ServiceNow.

I reviewed the date handling code. The bottom line is that a null value for sys_created_on will cause the partition logic to fail. The application cannot generate a valid date partition if it does not know the actual min and max dates for sys_created_on.

If the table is small (e.g. fewer than 100,000 rows) then the solution is to simply omit the partition parameter.

If the table is large then a possible solution is two loads: a partition load which excludes the null values, and a non-partition load which includes the nulls only. It should be possible to do this if we specify the filter value correctly on each load.

If it continues to fail then I should be able to figure out why and fix it by examining the stack dump and the YAML file.

Another option which should work is to specify a created date range

tables:
- {source: cmdb_ci, partition: month, created: [2001-01-01, 2019-01-01]}

or omit the upper end of the range and it will default to the current time

tables:
- {source: cmdb_ci, partition: month, created: [2001-01-01]}

But this will still skip the null records, which will need to be loaded separately:

tables:
- {source: cmdb_ci, filter: "sys_created_onISEMPTY"}

I'm exporting from Jakarta.

I tried rerunning without the partition directive and it completed without error.

I may have figured out why you're seeing different results when trying to reproduce the issue. Early on, I thought a record with no sys_created_on value was causing the issue. What I'm seeing now is an empty table (or unreadable by my account) causing an issue. I have datamart.check_readable set to false to deal with permissions issues we're still working through

With partitioning, check_readable=false, and an empty table, this happens:

22:33:59 INFO INIT Database cmdb_ci_service[cmdb_ci_service]: truncate table mydomain.cmdb_ci_service
22:33:59 INFO PROCESS Database cmdb_ci_service[cmdb_ci_service]: truncate table mydomain.cmdb_ci_service
22:33:59 DEBUG INIT PartSumTableReader cmdb_ci_service[cmdb_ci_service]: initialize query=""
22:33:59 DEBUG REQUEST JsonRequest cmdb_ci_service[cmdb_ci_service]: GET https://mydomain.service-now.com/api/now/stats/cmdb_ci_service?sysparm_query=&sysparm_count=true&sysparm_min_fields=sys_created_on&sysparm_max_fields=sys_created_on
22:33:59 DEBUG RESPONSE JsonRequest cmdb_ci_service[cmdb_ci_service]: status="HTTP/1.1 200 OK" contentType=application/json;charset=UTF-8 len=90
22:33:59 DEBUG PROCESS RestTableAPI cmdb_ci_service[cmdb_ci_service]: {"result":{"stats":{"min":{"sys_created_on":""},"max":{"sys_created_on":""},"count":"0"}}}
22:33:59 WARN PROCESS RestTableAPI cmdb_ci_service[cmdb_ci_service]: getStats query="" minCreated=null maxCreated=null
22:33:59 INFO PROCESS RestTableAPI cmdb_ci_service[cmdb_ci_service]: getStats query="" count=0 createdRange=[null,null]
22:33:59 DEBUG INIT PartSumTableReader cmdb_ci_service[cmdb_ci_service]: expected=0
Exception in thread "main" java.lang.AssertionError
        at servicenow.api.DatePartition.<init>(DatePartition.java:14)
        at servicenow.api.PartSumTableReader.initialize(PartSumTableReader.java:87)
        at servicenow.datamart.TableLoader.call(TableLoader.java:127)
        at servicenow.datamart.Loader.loadTables(Loader.java:94)
        at servicenow.datamart.Loader.main(Loader.java:59)

Yes. That makes perfect sense. PartSumTableReader (which is used by the partition directive) does not know how to deal with an empty (or unreadable) table, so it is aborting. I can fix that in the next release.

datamart.check_readable has no effect in SNDML3. I plan to add a similar feature, but the default value will be false (unlike SNDML2 where the default is true).

Thanks.

Marking this issue closed. I appreciate your testing with SQL Server since I do not use it so much. Feel free to email gileslewis3 at gmail directly if you have any feedback regarding sqltemplates.xml changes for SQL Server. Thanks.

gflewis / sndml3