Need help fixing bad data condition for database maintenance

[jhollender]

Hi,

I just noticed this error in our AC error log over the weekend:

The DELETE statement conflicted with the REFERENCE constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_BasketShipments", column 'AddressId'. The statement has been terminated.

I looked into the AC help page for this (http://help.ablecommerce.com/upgrades/a ... enance.htm), but I am unable to resolve the issue. I tried running the SQL query to fix it, and the preview works and shows 89 records. When I change the @preview variable to 0 to delete the records, I get the following error:

Msg 547, Level 16, State 0, Line 39
The UPDATE statement conflicted with the FOREIGN KEY constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "AbleCommerce", table "dbo.ac_Addresses", column 'AddressId'.
The statement has been terminated.

I'm not sure what to do now. Please help me resolve this.

[jmestep]

There is a patch out for that or you can run the scripts directly in the database -it's about half way down the page and there are also files for the patch.
http://help.ablecommerce.com/upgrades/a ... Fix%20User

[Katie]

If the patch that Judy linked to doesn't work, then there is another thing you can try.

Delete the constraint and then try deleting anonymous users. Once all anonymous users are cleaned we can add the constraint back.

In order to delete the constraint you have to run following script

Code: Select all

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
    ALTER TABLE ac_Orders
    DROP CONSTRAINT ac_Users_ac_Orders_FK1
END;

In order to add it back just run fixuserconstraints.aspx script and it will create it again.