Aderant Expert MA 'string or binary data would be truncated' error on export

The offending field won't be limited to that which has changed, as all values on the entity are included in the update operation.

Could there have been any changes to the db schema?

If you can, run

select * from INFORMATION_SCHEMA.COLUMNS

and check that the fields used by the employee connector don't have a smaller than expected character limit or data type. If you provide the output from that query I'll check it against the db schema in the connector code.

@beau All the attributes look fine to me.  Could I please have a version of the agent that writes the details of which attribute would be truncated?  The log file doesn't presently appear to contain this information, and it would be very helpful if it did.

Thanks Beau, that's great.  I'll check it out over the weekend and get back to you on Monday.

Hi Beau,

Sadly I don't have enough privs on the SQL server to enable debugging, so I tried the new DLL.  Here is the log file - it looks like it reports the UNIFYBroker DN of the failing object, but I can't see the details of which attribute would be truncated.

Could you please advise the next step?

UnifyLog20191201.zip

There should be an additional log file, at the path I specified above, not the normal broker log. Can you provide that?

Unfortunately no such file was created.  I've restarted the UNIFYBroker service several times (but not rebooted the server - is that necessary?)  I even tried creating the file manually and setting it to Full Access for all Authorised Users in case it was a security issue.

Can you confirm the path to the aderant-write.log  is relative to the UNIFYBroker Services folder?  Or is it elsewhere?

ExpertTooLong.zip

Thanks Beau, that's worked.  Here are the log files.  I see some HPPhoneNumbers are 33 characters - maybe they're too long?  But most records look fine, despite MIM reporting that all 2071 updates failed.

Look like the HPPhoneNumber is indeed the problem. If you refer to the output of the query I had you run before, PHONE_NO in the HBM_PERSNL table, which is what HPPhoneNumber is mapped to, has a max length of 17 characters.

The problematic value is two phone numbers separated by a slash, basically double the allowed length.

As to why all entities are failing to update, that is most likely due to the batched nature of these operations.

The previous Aderant Expert connector doesn't show this error (with the same source data and downstream system).  Do you know why it seems to be different in the new version?  My remit is to make minimal changes (the project is a IDB4 to UNIFYBroker 5 migration).

I've added a PowerShell transformation to truncate the HPPhoneNumber to 17 characters, but the error remains.

Here is the PowerShell transformation.  Do you have any idea which attribute is still too long?

I finally got in touch with the customer's DBA and he advises that the SQL server is SQL Server 2014 and so the trace flag 460 is not available because the SQL version is too old.

Nothing in the first custom log file seems to be a problem. Could there be more entities being updated since adding your PS transform?

The only other db string field being set that wouldn't be captured by that logging is ADDR_TYPE_CODE on the HBM_ADDRESS table. This value is set to a value specified in the connector configuration, 'Address Type' in the UI, 'typeCode' in extensibility. Length limit for this field is 5.

Address Type is "HA" in the connector configuration, so less than 5 characters.

Could you please send me an example of the SQL expression that the connector is using so I can execute it by hand with values substituted from just one users' entry?

Not possible. The actual queries being run are dynamically generated on multiple layers, not all of which are controlled by us. I couldn't determine the result if I wanted to. If you want to inspect the queries being run you'd have to see if Sql Server logs them, or perhaps some sort of tracing. I'm not sure what the options for this are, though.

OK no worries.  What do you suggest I do then?  This worked in the old Expert connector but it's not working now.  I need a timely solution to avoid budget overrun (it's a fixed price PO).

Provide a fresh aderant-write.log. I check to see if there's anything new that wasn't there before.

Not sure if this would be possible, but if you can setup a copy of the Export database on a server that supports the improved error message flag and export to that, it'd tell you what field is not right.

Other than that, try using LDP to send LDAP update requests to Broker. Find a entity which fails and modify its string fields until you can determine which one/s are causing the update to fail. I can provide a portable copy of ldp if you need.

Checked the log and those long HPPhoneNumbers (PHONE_NO) are all being sent to SQL as 17 characters now.

I am using PowerShell to load all the SQL data from that log so I can analyse field lengths.

I found one other field on one record that was too long, so added a reverse PowerShell transform to shorten it as well.  Now all the fields should be fine (until some long future data breaks the connector again).

Please close this ticket.

Good to here. Make sure to replace the debugging patch with the original dll if you haven't already.