Purpose & Scope:
How to use SQL to delete multiple Spitfire documents.
Procedure:
Note:This process is executed in SQL Server and is intended for use by the System Administrator. While implementing Spitfire, you may find a need to delete some Spitfire documents. For example, you create and test a SQL Workflow script that creates new documents on your projects, and your workflow script runs more times than you intended. Before you know it, you have quite a few copies of the same document.
Below is an example of a script that generates a list of delete statements that can then be copied to another Query Analyzer window and run to delete the list of documents. The script below is creating the delete statements for a specific Doc type (dhx.doctypekey= ‘E3FFE0BB-4611-443F-B8F8-AB0B7BA8F343‘) on a specific project (project = ‘GC091580‘) with DocNo greater than or equal to a specific number (docno >= ‘0100‘).
select
‘exec p_deleteDocument ‘‘‘ + cast(docmasterkey as varchar(44)) + ‘‘‘ — ‘ + dhx.titlefrom
xsfDocHeader dhx where dhx.doctypekey= ‘E3FFE0BB-4611-443F-B8F8-AB0B7BA8F343‘and
project = ‘21091580‘ and docno >= ‘0100‘
Steps:
- Write your select statement based on the script above modifying the ‘where‘ parameters to select the appropriate documents to be deleted.
- Be sure the output of your query is ‘to text‘.
- Run your script.
- Copy the output and paste it into another Query Analyzer window (be sure your new Query Analyzer window references the same Spitfire database).
- Execute your delete statements.
Additional Comments:
WARNING! Using this process permanently deletes Spitfire documents. There is no recovery.
KBA-01383; Last updated: October 12, 2016 at 8:46 am;
Keywords: SQL; deleting documents from Spitfire