Web Tools Sync Errors

[Former Member]

Hi Experts,

I am receiving this error on 25 orders in my webtools sync Business One queue detail:

The DELETE statement conflicted with the REFERENCE constraint "FK_ShipmentDetail_OrderDetail". The conflict occurred in database "SDLB1WebTools", table "dbo.ShipmentDetail", column 'OrderDetailID'.

The statement has been terminated.

at netpoint.api.data.DataFunctions.Execute(String SQL, IDataParameter[] parameters, String connectionstring)

at netpoint.api.NPBase.Delete(Boolean logTransaction)

at netpoint.api.commerce.NPOrderDetail.Delete(Boolean logTransaction)

at NetPoint.SynchSBO.SBOObjects.SBODocument.SBO_2_NP(Documents& document, NPOrder order)

at NetPoint.SynchSBO.SBOObjects.SBOOrder.SBOToNetPoint(SBOQueueObject qData)

at NetPoint.SynchSBO.SynchObjectBase.Synch()

Can anyone tell me what I need to do to stop the error messages?

Thanks and Regards

Jon

There is also this error occuring in deliveries:

Can not find NetPoint order for Delivery Document#88251

at NetPoint.SynchSBO.SBOObjects.SBODelivery.SBOToNetPoint(SBOQueueObject qData)

at NetPoint.SynchSBO.SynchObjectBase.Synch()

Edited by: Johnny Yen on Dec 11, 2009 1:34 PM

[Former Member · Accepted solution]

Hi Jon ....

The first error is caused when you have a delivery associated with an order that synchs to WebTools and then the order is updated in B1 so the order tries to synch to WebTools. When an order synchs to WebTools it deletes the detail lines and recreates based on the lines in B1, but when there is a delivery these order lines are referenced in the ShipmentDetail table and the database table relationships will not allow deletion of the order lines.

To get rid of the error, the easiest thing to do assuming nothing on the order significantly changed and you really don't care if it synchs to WebTools is to manually remove the record from the Transaction Queue table in SBO so the synch process doesn't try to synch the data. The table is called PRX_TransactionQueue, look for records with object type 17 (orders) ,failed status and the appropriate error message. Once you find these records just delete them.

If the order was changed significantly (items added or removed, etc) then you could try manually deleting the Delivery information from the WebTools tables (Shipment, ShipmentDetail) and this should let the Order synch and then you should re-queue the Delivery in SBO so it will synch (again). I've never had to do this myself so I'm not 100% sure what else you may run into so try on a test environment first.

The second error is usually caused when the Order in SBO that the Delivery is based on is closed when initially synched. The Order will appear to synch to WebTools fine but the final step in that process is to update the WebTools Order UDF in SBO with the OrderID from WebTools. If the order is closed, this final step fails. When the SBO Order is missing the WebTools Order UDF value, the SBO Delivery doesn't know what Order in WebTools to link the delivery to and that's why we get this error.

To fix you must find your Order in the WebTools OrderMaster table. Look for a record with CartType O and the SynchId field will be the DocEntry of the B1 Order. Once you find it do a quick check of order total and lines to make sure it's right. Now the OrderID of this record is what should go in the SBO UDF for that order. I believe the actual DB field is U_PRX_SID. This normally happens for older orders in SBO that have already been processed so I usually ask my client if they care about synching this old data to WebTools. If not, I remove older orders, invoices, deliveries from the PRX_TransactionQueue before I run my initial synch.

Hope this helps

[Former Member · Accepted solution]

BTW ... sorry about the format of my last post. It stripped out my paragraphs for some reason.

[Former Member · Accepted solution]

Hi Stephen,

The reason is:

There are maximum characters allowed to get correct format. You may need to split to two threads in order others to read easily.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Stephen,

Thanks for the reply. How do I go about finding and deleting the records?

Thanks

Jon

[Former Member · Accepted solution]

Hi Jon .. are you familiar with SQL Server and running SQL queries? This would be required at a minimum.

Basically there are 2 databases you need to deal with, the SBO database on the SBO SQL Server and the WebTools database which could be on the webserver or there could be a dedicated SQL Server for the web database.

On the SBO database there is a transaction queue table created by WebTools called PRX_TransactionQueue. Whenever an action is performed in SBO (Add/Update/Delete) a record is added to this table. The record structure is generic but the column called ObjectType contains an integer indicating what type of record it is. These are standard SBO object types (eg.2 - BP's, 17 - Orders, 15 - Deliveries, etc). There is another column that contain the key for the record ... this will be the primary key from the records base table (eg. 17 - Orders are from ORDR table). There is also a Status column indicating if the record was just added (NULL), if it failed during the last synch step (Failed) or if it was set to not synch anymore from the synch manager (Skip).

Anyway, if you just wanted to remove the transaction records for the Orders that are giving you the Foreign key errors because the delivery has already synched. You can open the PRX_TransactionQueue table in SQL, find the records with ObjectType 17 and failed status with those error messages and just delete them from the table. You coudl also do this in a SQL query like this

DELETE FROM PRX_TransactionQueue WHERE ObjectType = 17 AND SynchStatus = 'Failed' AND SynchMessage Like '%Foreign Key Error Message%'

You will need to play with the SQL, I just took that off the top of my head. The last part makes sure we are only removing those records that are erroring because of the delivery already synching and it will leave other errors that should be reviewed separately.

The WebTools database is needed to fix the other set of error messages .. but one thing at a time lol.

[Former Member · Accepted solution]

Hi Stephen

Yes - now very familiar with creating and running SQL queries, although most of my work tends to be in the query generator of SBO.

I have composed and executed the query you suggested in my sand box system and it appears to work fine. The errors regarding the Foreign Key now do not appear. However, I am a litle worried about running this on my liive system as my understanding up to now is that I should not mess with anything in the SBO databases which involves Add/Delete or Update queries. Is this not the case here because it is a PRX table?

Regards

Jon

Edited by: Johnny Yen on Dec 15, 2009 8:55 PM

[Former Member · Accepted solution]

Correct ... this PRX_TransactionQueue table is not a B1 table. It was created when you click the "Install Plugin" of the synch manager so modifying it will not break anything in SBO. At worst there will be some data in SBO that doesn't synch to WebTools becuase you deleted the record or modified the values.

If you are interested, there is a SBO stored proc called SBOTransactionNotification (or somethign similar) that does the inserting into the table. In the past I have written my own queries to add data to that table so I can force a synch on some documents.

Steve

[Former Member]

If the data to be deleted is having dependence i.e some other data is refered by it then first of all we have to delete the dependent data then only can delete the one needed