PDXpert PLM Software
Application Note

Keep system settings and remove test item records

Last update 2021-12-13

This is an advanced topic for PDXpert 9.0 and later. This removes all document, part, change form, and file attachment records from your database. You must have Windows administrator permissions, and be comfortable using SQL Server Management Studio to run the SQL queries.

Introduction§

As you design a new PDXpert system configuration, the best way to protect your work is:

  1. Make a backup of the database with its current settings. Note the backup content for Step 5.

  2. Set the new system rules and collection members for test.

  3. Make a backup of the database with its new settings. Note the backup content for Step 5.

  4. Test the new configuration with example items to confirm the settings are correct.

    Items are parts, documents, change forms, and their item and revision file attachments.

  5. Restore a database backup (without test data from Step 4).

    • If the test fails, restore the database backup from Step 1.

    • If test is successful, restore the database backup from Step 3.

  6. If you want to make further settings changes, go to Step 2 and repeat.

If Steps 3 and 5 were skipped, use this procedure to recover a configuration that meets your needs, but without any test items.

Procedure

Before you begin, back up your system. This procedure deletes documents, parts, change forms and files data. You cannot undo the changes it makes. If there's an error, or you don't like the results, then you must restore your backup.

Close the PDXpert Application Server after you back up the system.

All users must exit the system before starting this procedure.

This procedure deletes all file attachment records, except for those file records attached to Organization collection members.

If you've attached any external links or files to Organization collection members and you want to empty the \Library folder in Step 5, then use this query in SQL Server Management Studio to find all files attached to Organization collection members.

SELECT

  BOOrg.[Name] AS [Organization]

  ,PFile.[Name] AS [FileName]

  ,PFile.[FileId] AS [LibraryName]

FROM

  BusinessObject BOOrg

  INNER JOIN OrganizationFile OrgF ON OrgF.[Parent] = BOOrg.[Id]

  INNER JOIN PhysicalFile PFile ON PFile.[Id] = OrgF.[PhysicalFile]

ORDER BY

  BOOrg.[Name], PFile.[Name]

Using the results of this query, use one of these options:

  • In the PDXpert client, use the Organization list to copy (if needed) each file attachment, and then delete it from the organization Files list. After you've deleted all files in Step 5, you can re-attach the saved files to their original organization records.
  • In the PDXpert Server's \Library folder, use the LibraryName list to find and copy files into another folder. After you've deleted all files in Step 5, copy the saved files back into the \Library folder.

Test the complete process carefully before permanently deleting any data or files.

  1. Open SQL Server Management Studio, connect to the PDXpertDB database, and run the following query:

    -- Delete item records

    DISABLE TRIGGER ALL ON [dbo].ChildItem;

    DISABLE TRIGGER ALL ON [dbo].Item;

    DELETE [dbo].Item;

    ENABLE TRIGGER ALL ON [dbo].Item;

    ENABLE TRIGGER ALL ON [dbo].ChildItem;

     

    --Delete PhysicalFile attached to items (not Organizations)

    DELETE P FROM [dbo].PhysicalFile P

    LEFT OUTER JOIN [dbo].OrganizationFile O ON O.PhysicalFile=P.Id

    WHERE O.PhysicalFile IS NULL;

     

    --Change DBID

    UPDATE [dbo].PDXpertInfo SET DatabaseId=newid();

     

    --Delete indexes

    DELETE [dbo].ItemIndexQueue;

    DELETE [dbo].FileIndexQueue;

    DELETE [dbo].FilterQueue;

    DELETE [dbo].ChangedObjects;

  2. Restart the PDXpert Server service or reboot Windows.

  3. In the PDXpert client:

    1. Select the Tools ➔ Index Status... command, then click the Re-index button (finishes instantly). Searching for items should not show any rows.

    2. Review all system settings using the Tools ➔ System Rules... command, as well as a reasonable sample of the collection members. If needed, reset the Next number values for identifier sequence members.

  4. Use the PDXpert Application Server to make a backup, so that you can restore this configuration later.

  5. In Windows File Explorer, delete all files in the \Library folder only if no Organizations collection members have file attachments.

    If you saved the member files before you started this procedure, you can now re-attach or copy the saved files.

 

This application note was relevant to the PDXpert software release that was current at time of publication. Product changes since that time may affect its utility. We'd be happy to assist you in assessing the applicability of this note to your situation.

Working within PDXpert
Working with other software applications