On rare occasions I’ve found it necessary to use SQL statements to remove a project from the Deltek Cobra database. This can happen if the database gets corrupted in some way and the project can’t be managed by normal means. This article offers some ideas and a sample SQL statement that could be helpful in this situation.
It’s only happened to me once in eight years when the computer I was using dropped its network connection during a project restore; leaving only fragments of the project scattered among some of the database tables. Unable to delete or restore the project, I had no choice but to roll up my sleeves and dial in through Cobra’s SQL Command Utility feature to delete all the orphaned project records. Alas something similar happened to one of my customers recently, prompting me to write a SQL script that removes a problematic project from the database tables.
I’m no SQL expert, but know enough basics to bail myself out of trouble at a pinch. The following SQL statement is one I compiled based on my own research of the database tables. Therefore, if any SQL experts out there know a better way to remove a Cobra project, please let me know and I’ll be happy to publish your ideas.
Disclaimer:
By using the contents of this article you are agreeing to do so entirely at your own risk. Ten Six Consulting, LLC cannot be held responsible for any undesirable outcome from this action.
IMPORTANT: Always be sure to back up your database before performing any changes in the SQL Command Utility as this bypasses Cobra’s referential integrity functions and can potentially corrupt your data.
The SQL Script:
Click here to download a zip of this SQL script file.
Note: you will need to change the project name to that of the project you wish to remove from your Cobra database. This script will only work on Cobra 5.1 SP2 or later databases and is only tested on Microsoft SQL Server 2005: adjustments may need to be made for an Oracle database.
Instructions:
- Prepare the SQL Script file
- Open the DeleteProject.SQL file in Notepad.
- Click on Edit and choose Replace…
- In the Find what: field, enter DEMO.
- In the Replace with: field, enter the name of the project you wish to delete.
- Click Replace All.
- Click Cancel to close the Replace dialog.
- Verify that your project name has been inserted between the quotes.
Running the SQL Script in Cobra:
In this next example we will use the DeleteProject.sql script to remove the DEMO project from a Cobra system. This will be done by running the SQL Command Utility feature in Cobra.
- Click on the Tools menu in Cobra and choose the SQL Command Utility… option.
- In the SQL Command Utility dialog, click the Open button.
- Locate your modified DeleteProject.sql file and open it into the SQL Command Utility SQL Statement window.
- Click on the Execute button.
- When the process is complete, scroll down the Messages window to make sure there were no errors reported.
- Close the SQL Command Utility dialog.
- Exit Cobra.
- Restart Cobra.
After the restart, you will see that the target project has been removed from the Projects tab.
Anyway, while I hope you never have to do something like this, the above example may prove helpful in a pinch.
UPDATE: December 12, 2012
We just had a reader send in an improved SQL script to the one above that you can read about and download by clicking here.