Hi, I want to cleanup all the orders in Commerce Server 2007 from SQL Server. To get this, can I delete the data from "OrderForms", "PurchaseOrders" and "OrderTemplatesAndBaskets" tables (in transaction DB) directly from SQL Server or should I do it by another way?. In that case, I'm missing any table?
Thanks
Scherzo,
As I understand it, "orders" really fall into three groups:
1.) Baskets (i.e. Shopping Carts)
2.) Saved Templates (i.e. Shopping Lists)
3.) Purchase Orders (i.e. Orders that have actually been submitted)
Baskets and Shopping Lists are found in the OrderTemplatesAndBaskets table.
Purchase Orders are spread across four tables: LineItems, OrderAddresses, OrderForms, and PurchaseOrders. Because of the Foreign Key relationships in the database I believe you need to delete records from these four tables in this order:
LineItems --> OrderForms --> OrderAddresses --> PurchaseOrders
Does that help?
Regards,
Glen Fergo
Scherzo,
You should not delete data directly in SQL. Use the PurgeCommerceData.exe tool instead. You can find it in %Commerce_Server_Root%\Tools
Hope this helps,
David
If you choose to use the PurgeCommerceData.exe tool as David suggests, I would also run SQL Profiler.
See what data gets deleted, from what tables, and in what order.
The more you know about how the platform actually works, the better off you are.
Regards,
Glen
Thanks to both for the comments. Goot to know that PurgeCommerceData.exe exists.
This application will delete only orders related data or in the other hand will purge all Commerce data (catalogs, etc)? If no, there is a similar application that purge all Commerce data? (something like initialize Commerce DB without destroying DB structure))
Thanks.
It deletes a variety of things:
D:\Program Files\Microsoft Commerce Server 2007\Tools > PurgeCommerceData.exe /?
Usage: PurgeCommerceData <sitename> <-b | -p | -m | -c> [options]
-b -d <age_in_days> [-n name]
Purges all baskets that have been unchanged for the last d days. If the name option is specified, d
eletes only baskets with the specified basket name.
-p -d <age_in_days> [-s status]
Purges all purchase orders that have been unchanged for the last d days. If status is specified, de
letes only POs with the specified order status value.
-m -d <age_in_days> [-o]
Purges all Marketing System entities (Customers, Campaigns, Campaign Items, and Promotion Codes) tha
t were deleted at least d days in the past. If the -o option is specified, promotion codes for old
discounts (those that are associated with discounts that expired at least d days in the past) are al
so deleted from the database.
-c
Purges records of all deleted products, categories, and inventory skus from the Catalog and Inventor
y systems.
There's not really a tool that "initialize Commerce DB without destroying DB structure."
Hope this helps,
David
