Skip to content

Troubleshooting the SQL Connector

Each entry is titled by what you see on a run, an Activity or a connection test. For a run that ends with a warning or an error whatever the Connector, see Troubleshooting Synchronisation.

Where a SQL Connected System usually goes wrong Work down four checks. Does the connection test pass? If it fails on the certificate (SQL Server), trust the certificate from the card the test shows. If it fails otherwise, check the host, port, database name, service name or SID, and password: the driver's message says which. If it passes, does the schema import see every table? If not, grant the account SELECT on the table, or correct its name or schema. If it does, are deleted rows still in JIM after a Delta Import? If so, Watermark Column mode cannot see deletions: use a change-log table or schedule a Full Import. If not, does an export report that no row was written? If so, check the table's triggers and the account's grants. If not, collect the Activity, the worker's log and the Object Types document, and ask for help. no, the certificate no, otherwise yes no yes yes no yes no Connection test passes? Schema import sees every table? Deleted rows still in JIM?after a Delta Import Export writes no row?"No row was written ..." Trust the certificatefrom the card the test shows Check host, name, passwordthe driver's message says which Grant SELECT on itor correct its name or schema Use a change-log tableor schedule a Full Import Check triggers and grantson the table Still stuckcollect the Activity, log and document Where a SQL Connected System usually goes wrong Work down four checks. Does the connection test pass? If it fails on the certificate (SQL Server), trust the certificate from the card the test shows. If it fails otherwise, check the host, port, database name, service name or SID, and password: the driver's message says which. If it passes, does the schema import see every table? If not, grant the account SELECT on the table, or correct its name or schema. If it does, are deleted rows still in JIM after a Delta Import? If so, Watermark Column mode cannot see deletions: use a change-log table or schedule a Full Import. If not, does an export report that no row was written? If so, check the table's triggers and the account's grants. If not, collect the Activity, the worker's log and the Object Types document, and ask for help. no, the certificate no, otherwise yes no yes yes no yes no Connection test passes? Trust the certificatefrom the card the test shows Check host, name, passwordthe driver's message says which Schema import sees every table? Grant SELECT on itor correct its name or schema Deleted rows still in JIM?after a Delta Import Use a change-log tableor schedule a Full Import Export writes no row?"No row was written ..." Check triggers and grantson the table Still stuckcollect the Activity, log and document

"Unable to connect. Message: ..."

The database driver's own message follows. A wrong host or blocked port times out after the Connection Timeout; a wrong database name, service name or SID is rejected immediately in the database's own words; a wrong password is an authentication failure. On Oracle, check you have chosen the right form (service name or SID) as well as the right value.

On Oracle with Native Network Encryption, ORA-12660 (encryption or crypto-checksumming parameters incompatible) or ORA-12650 (no common encryption or data integrity algorithm) means the server will not encrypt the way JIM requires. Check its sqlnet.ora against What an Oracle server needs for Native Network Encryption. The driver reports both as ORA-50201 ("failed to parse connect string"), which JIM shows after the cause; it does not mean the host or service name is wrong.

The connection test fails on the certificate (SQL Server)

See When the connection test fails on the certificate.

"Object Type 'X' reads from HR.TABLE, which this database account cannot see"

The name or schema is wrong, or the account has not been granted SELECT on it. On Oracle, grants are per table; see Database account permissions.

"Object Type 'X' names 'TABLE', which exists in more than one schema"

Add "schema" to that Object Type.

An attribute is missing after the schema import

Check the import's warnings: a column whose type JIM cannot synchronise is named there. Expose it through a view that casts it to a supported type.

"The value has a fractional part, and this attribute's data type is Number"

The column holds fractional values but the attribute's type is a whole number, usually because the type was changed on the Schema tab. Change it to Decimal, or correct the source data.

A Delta Import ran as a Full Import

Expected on the first run, and after changing the Delta Import Mode. If it happens every time, read the warning on the Activity for the reason.

Deleted rows are not removed by a Delta Import

Watermark Column mode cannot see deletions. Use a change-log table, or schedule a periodic Full Import. See Delta Import with a watermark column.

An export reports "No row was written ... though the database raised no error"

A trigger or rule on the table discarded the insert, or the account cannot write to the table. Check the table's triggers and the account's grants.

An export reports "No row ... is identified by this Connected System Object's external ID"

The row was deleted or re-keyed outside JIM. Run a Full Import to bring JIM back in line with the table.

An Oracle export reports "returned no value for its anchor column"

The table's key is neither flowed by a Synchronisation Rule nor generated by the database. Back the column with a sequence-based default or an identity column, or flow the key from JIM.

Dates are out by an hour

The column carries no offset and the Database Time Zone does not match what the application records. Set it to the zone the application uses; columns that carry their own offset are unaffected.

Still stuck?

Collect these before asking for help:

  • The database and its version, and whether JIM connects with encryption.
  • The run's Activity, with its message and one failing execution item's error.
  • The worker's log from the time of the run: docker logs jim.worker, or sudo podman logs jim-worker.
  • The Object Types document, and for a permission problem the account's grants on the tables involved; leave its password out.

Then ask in the Q&A category of GitHub Discussions, or open a GitHub Issue for a bug.