• Quickbooks replication errors - String would be truncated; Violation of UNIQUE KEY constraint

Quickbooks replication errors - String would be truncated; Violation of UNIQUE KEY constraint

  • 47Views
  • Last Post 5 weeks ago
  • Topic Is Solved
0
votes
Dominic Santini posted this 07 April 2023

Hello. With our first replication package tests (QBO to SQL Server) we are receiving two sets of errors:

1. String or binary data would be truncated.

BTW it would be rather helpful if the log noted which field caused the error. 

In this case the error is because Skyvia creates the EntityRefName field with a length of 100 chars, but our data occasionally exceeds this.

2. Violation of UNIQUE KEY constraint 'xxx_Customer_1'. Cannot insert duplicate key in object...

In this case Skyvia is, strangely, creating an additional unique key constraint on the destination Customer table, on the field DisplayName. This makes no sense as there already is a unique key constraint on the Id field, like with all tables. 

For whatever reason our accountants create multiple customers with different fully qualified names but the same display name. Like "UNB" for the display name several times for different customers. 

Are these issues that can be addressed? 

Thanks.

field-truncated unique-key-constraint
Order By: Standard | Newest | Votes
0
votes
Yevheniia Bilotserkovska posted this 10 April 2023

Hello Dominic.

Thank you for contacting us.

Could you please share the Integration Id you have an issue with?

1. Regarding String or binary data would be truncated, we will increase the length in the metadata for this field and let you know when it is done. 

Alternatively, you can temporarily exclude the EntityRefName field from the replication task.

2. Regarding the "Violation of UNIQUE KEY constraint 'xxx_Customer_1'. Cannot insert duplicate key in object" error - we will check the issue with our developers and let you know the result.

Please share the Integration Id for our further investigations.

We are looking forward to hearing from you.

Best regards,

Yevheniia Bilotserkovska.

0
votes
Dominic Santini posted this 10 April 2023

Hi Yevheniia -

On #1, understood. I'll wait to hear from you. I can temporarily exclude this field for testing now, but it is one that we would require for the replication to work.

On #2, some example run IDs:
130111518
130111605

And example replication package IDs:
213228
213232

Here is an example of the constraint which Skyvia is creating in our destination table causing the error:

ADD  CONSTRAINT [KEY_Skyvia.216613801ColombiaT3_Customer_1] UNIQUE NONCLUSTERED 

(

[DisplayName] ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Thanks.

0
votes
Yevheniia Bilotserkovska posted this 11 April 2023

Hello Dominic.

Thank you for your reply.

Our developers are investigating your case and we will let you know the result as soon as we get a response from them.

Best regards,

Yevheniia Bilotserkovska.

0
votes
Dominic Santini posted this 11 April 2023

Thanks, Yevheniia. Look forward to hearing from you.

0
votes
Dominic Santini posted this 18 April 2023

Hi Yevheniia -

Any info yet from the developers on the index/key problem above? 

As with the other issue (field to be added in next release) I am kind of on hold in my evaluation while we wait for these issues to be sorted out. 

With #1 (increasing the field length in metadata), do those kinds of changes get made relatively quickly or are they held for the next relase? 

Thanks.

- Dominic

0
votes
Yevheniia Bilotserkovska posted this 19 April 2023

 

Hello Dominic.

Thank you for your patience and waiting.

We have got some updates from developers, please see below:

1. Regarding String or binary data would be truncated - it is still in progress, we will keep you informed about the status. Our technical team is doing their best to solve this case ASAP but unfortunately the solution requires more time than we have expected. 

2. Regarding the "Violation of UNIQUE KEY constraint 'xxx_Customer_1'. Cannot insert duplicate key in object" error - could you please check whether the issue still persists or not and let us know the result?

 

Regarding the field you asked to add in a separate request - it is already supported, we will reply to you on a separate topic, please check.

Best regards,

Yevheniia Bilotserkovska.

 

0
votes
Dominic Santini posted this 20 April 2023

Hi Yevheniia -

Thanks for the update. On #1 understood, I will keep waiting.

On #2 I confirm that this error is gone after re-running the packages with drop tables enabled.

Thanks!

- Dominic

0
votes
Yevheniia Bilotserkovska posted this 20 April 2023

 

Hello Dominic.

Thank you for the confirmation regarding a resolution of the #2 issue.

As soon as we receive an update on case #1, we will notify you immediately.

Best regards,

Yevheniia Bilotserkovska.

0
votes
Yevheniia Bilotserkovska posted this 5 weeks ago

Hello Dominic.

Thank you for your patience and waiting.

'String or binary data would be truncated' error it has been already fixed.

Could you please check and let us know the result?

Best regards,

Yevheniia Bilotserkovska.

New Ticket
Categories
Popular Tags
Close