When outside forces inflict damage on your Deltek Cobra system, a working knowledge of basic SQL can be a life saver.
Imagine my horror as I watched my client’s Deltek Cobra production system open, with the Project list table completely bereft of the project we’d been working on for the last 7 months.
To set the scene a little here, you should know that the Deltek Cobra client is installed on a Wi-Fi networked machine and the Cobra database is on a small server elsewhere in the building. During a restore of the program the Wi-Fi evidently bailed out on me.
That is just about the worst time to lose your connection to the database. Sure enough, after I resuscitated the Wi-Fi and restarted Cobra, our beloved project was nowhere to be found. The real horror started when I attempted, once more, to restore the program from the .CMP backup file. It failed horribly with various ambiguous messages about not being able to increment some ID number; palms beginning to sweat – blood rushing from face. I’ve never seen a restore fail in Cobra – ever. That function is pretty much bullet proof.
I’m an avid backer-upper, to the point of paranoia, but that doesn’t help anyone when the backup won’t restore. I knew the backup file was good because I’d loaded the project on a sandbox machine earlier from that same .CMP file. This stank of database referential integrity problems for sure.
To the rescue – the Cobra SQL Command utility. I ran some SELECT * statements on the Cobra main data tables and found bits of my eviscerated project scattered around. Clearly not enough of it survived the crash to work as a project, but enough was left in there to crap out the restore function.
The only solution in a situation like this is to roll up one’s sleeves, dive in there and start launching DELETE statements at any table that references your project. Now I’m by no means an SQL expert, but have learned enough over the years to bail myself out of some otherwise very hairy situations. And keep in mind that Cobra ships with a set of very handy SQL files that I often modify for my own nefarious purposes. Check out the Samples/Scripts directory below the Cobra 5.0 install directory to find these useful files.
The following are the tables from which I had to delete my project. These may be helpful if you’re ever in a similar situation.
DELETE FROM TPHASE WHERE PROGRAM = ‘Project Name ‘
DELETE FROM CAWP WHERE PROGRAM = ”Project Name ‘
DELETE FROM BASEDETL WHERE PROGRAM = ”Project Name ‘
DELETE FROM BASEHIST WHERE PROGRAM = ”Project Name ‘
DELETE FROM BASEHIST WHERE PROGRAM = ”Project Name ‘
DELETE FROM BASELOG WHERE PROGRAM = ”Project Name ‘
DELETE FROM CODEFIELD WHERE DIR_ID = ”Project Name ‘
DELETE FROM COSTELEM WHERE PROGRAM = ”Project Name ‘
DELETE FROM COSTREPCLASS WHERE PROGRAM = ”Project Name ‘
DELETE FROM LINK WHERE PROGRAM = ”Project Name ‘
DELETE FROM PROGRAM WHERE PROGRAM = ”Project Name ‘
DELETE FROM CLASSES WHERE PROGRAM = ”Project Name ‘
If your project has a more sophisticated setup, there may be other tables involved, so I recommend you go through all of them with a select statement first and note those that show your project name.
After this set of statements had been run, the project restored just fine and the emergency was averted. If you have the administrative rights, or a friendly DB administrator to help, there are undoubtedly better ways to clean databases of broken records, but at a pinch the above is one solution you might consider.