SQL Access

[northwestmsp]

So we found out after a major data corruption and a 30 day old backup (I know, I know...), that the standard CommitCRM Advantage Database can be easily worked on through the Advantage Data Architect program. I have used this software for other projects in the past and decided to give it a whirl to attempt to recover our data.

Long story short, the tickets database was corrupt, but no others. We were able to write a program to re-create, map, link and update almost every ticket from the lost 30 days based on the information in the other tables. All is well and we're back in business. With that said, the ability to quickly modify data and have SQL access to our data has spurred all kinds of creativity around here. Like this quick command we're using to re-map all document paths as we're moving to a new server:

SELECT REPLACE(DOCUMENT_NAME,'\\oldserver','\\newserver') from DOCS

My point here is that having access to the SQL data behind the scenes and the ability to MODIFY that data has been a huge help to our company, probably saved us thousands in lost revenue from the corruption. We're considering moving forward to the "SQL" version of Commit, but are wondering if we'll have the same access to our data.

Obviously this is for advanced users only, I would never recommend someone without extensive database/SQL admin experience even poke around their CommitCRM data. But with that said, for those of us with the experience, know-how and desire, do we have the option to access the SQL data in WRITE mode with the new SQL upgrade?

[lpopejoy]

Why wouldn't you have the same access to your data? Advantage Data Architect works for me (running SQL version)... ...and I can edit, but you know CommitCRM is going to hate you for that, right? Well, maybe it won't be that bad...

[Support Team]

@lpopejoy said it correct. You should never use SQL to update the data directly.

In the case of @exbabylon - they had a special situation and probably probably had no other choice with data restoration so I cannot comment much on this special circumstances.

Yet again, no one should ever use direct SQL to perform any direct database inserts, updates or deletes. programming API should be used for this instead. Nothing else. Many times data is spread across different tables and had links or "hidden" information you cannot be aware of. This will result in breaking the database integrity and there will be no way for us to help. Please, use SQL to query information as much as you need but never to perform any modifications. Our API has the "knowledge" on how to handle updates correctly.

BTW - re the documents SQL command you listed - we have a special utility that does this and a few other updates regarding path changes. It is the one that should solely be used and NOT a direct SQL command.

Thanks.

[northwestmsp]

Did not mean to cause any issues with the team at CommitCRM on this. As technicians, developers and IT guys we're constantly searching the a workaround, backdoor access or another way to get something done when the path straight ahead doesn't work. Just found the whole experience interesting and wanted to share with the community as a whole in case it helped someone else out somewhere else.

With that said, I will re-iterate and stand by CommitCRM on this... ALWAYS use the API. The information I've provided is merely anecdotal and should only be considered in a situation like ours when you have no other option.

[Support Team]

Sure! We're actually happy that you shared your experience here and were really impressed with your creativity! Thanks.