Topic

Messages

Database Problems

Database Problems

How many users have experienced database problems and could you tell me the type of database problems you have experienced.

Thank you,

Michael Thomas Controller Fuse Inc.

We've been using C&P for over a year and a half, and had experienced problems with our database until recently (knock wood). We believe the root of the problem stemmed from frequent crashes at one workstation while C&P was open on the network.

There was also a Windows issue with this same workstation which was remedied with a download "service pack" from Windows. Sorry I cannot provide more technical detail - I'm just the office manager/bookkeeper.

Once all our users became accustomed to the impact of network crashes on C&P, everyone became more diligent about "quitting" out of C&P and not "shutting down" while C&P was open.

In the meantime, I learned to be more protective of the database by backing up several times throughout the day, in case we lost data due to database damage.

It took us a long time (months) to narrow down the problems and eliminate possible causes.

Ultimately, we had to do an export; import utility to fully repair the damaged data.

Things have been fine ever since.

Good Luck - I hope this helps.

Cathy Yonki KH Advertising, Inc.

Hi Michael,

We have experienced numerous database problems! We have run the Database Repair and Reindex at least five times in the past 10 months. Not to mention the Import/Export utility downloaded from the C&P website.

Usually we discover the database problem when we are doing billing and the costs printed on our reports do not match the costs that are pulled in on the invoice. Either that, or, a "lock" appears on the screen and someone is forced to Force Quit and the information that was being worked on is faulty, i.e. incorrect amounts or an invoice cannot be edited of deleted.

Each time that we've tried to verify and recover the job ticket we are given a dialog box explaining that one or another of the database files is damaged, i.e. COSTS or WORKING. We've been told by Client and Profits that it is due to our database residing on a shared server. They suggest that for C&P to run optimally it needs to be on a dedicated server, which is something we were never told by our original consultant. Just recently we have moved the database to a dedicated server (simply, a Mac G3 Computer) and are assessing it's performance moving forward.

I would be very interested in hearing any experience you have had with database problems. And please forgive me for my less than technical description!

Thanks,

Jennifer Savino Accounting/HR Manager Denmark Advertising & PR

Jennifer,

The "lock" indicates that the record you're trying to edit or update is being used by someone else. The "padlock" cursor is a message that indicates record locking, which is the method Clients & Profits uses to prevent two people from saving changes to the same record at the same time -- which is critical with multi-user databases.

A record will only be locked as long as the other user is editing it. Once the user saves their work, the padlock goes away and you can then edit the newly-updated record. There's no limit to how long a record can stay padlocked, since it depends on how long the other user takes to make their changes (for example, they could be editing a job then take a phone call and not realize 30 minutes went by).

Turning off your computer while the cursor is padlocked is dangerous because the server keeps that record open thinking it's about to be updated, which doesn't happen because your computer has broken the connection (by turning it off). Depending on how often this happens, the database can become corrupted. The proper way to cancel a padlock is to press command-period (on a Mac) or control-break (on Windows) then close the window. You can try again to edit the record any time.

Record locking -- as well as the causes of data corruption -- are well documented in the Clients & Profits Database Guide. A printed copy was included with your documentation, but you can also download it here:

http://www.cnp-x.com/support/database_guide.html

Mark Robillard Developer, Clients & Profits

Hi Michael,

We are currently using Classic 5.0.4 and have been having major database problems for the past month or so. The Costs file seems to be the one that is repeatedly getting damaged. I have tried repair, reindex and then last week an export/import fix followed by the Time X fixer and now this week problems are back.

I realized that we upgraded to 5.0.4 around the time that these consistent database problems started happening, but have no idea if that's related.

We are all Mac and use server software 10.2.1.

What kind of problems are you having? I can bore you with more details if you'd like. These problems are eating up the majority of my time these days and it's pretty annoying!

Catherine Colangelo Business Manager The Phillips Agency

I am having major problems with time not getting to the job summaries. There is no common link with staff, tasks, dates - very random. I have to do verify and recovers every day. The job summaries are not reliable unless I do a V&R. Very frustrating and time consuming. We have re-indexed the database and that has not corrected the problem. Anyone else having this problem?

DonnaLiebert Accounting Manager ------------------------- Kelliher Samets Volk

Hi Michael,

We have used C&P since August 1999 on a DELL Pentium II 400MHz server. The server runs Microsoft Back Office Small Business server which is includes Exchange Server, DHCP Server, Proxy Server, Print Server, and File Server. We keep our Studio Files on a separate Mac Server. There are about 16 PC and 8 Mac users.

Haven't had any major problems in the last 4-5 years. Needless to say the system is very stable. We have only 5 C&P User licenses and trust me we use them all 5 licenses at any given time.

Hope this helps.

Johnson Paul Controller Campbell Michener & Lee Incorporated

Michael,

Early on, we had problems which were resolved by putting the C&P database on its own server. You need to do this -- it is in the documentation somewhere, but probably it should be highlighted in red and appear up near the front of the installation instructions. Since we moved the database onto a dedicated server (over 2 years ago) it has been pretty stable (knock wood).

Since the database can be damaged when a computer that is connected to it crashes, here are a few other important safeguards that we implemented successfully.

- When logging onto the C&P server, one should first log off of any other servers. The other server freezing or going down could lock up your computer, and thereby damage the C&P database. - When people aren't using C&P, they should close the program and log off the server. That way, if they should experience problems on their machine, it won't damage the database.

Come to think of it, I'm going to remind everyone of this right now.

Adam

-- Adam Schnitzler Creative Director S3 www.s3s3s3.com

Much to my frustration, we had a weekly import/export routine going for awhile due to damaged database messages. Then we moved the C&P database to a dedicated G3 server with nothing else running on it, replaced most of our cables, which were pretty old, moved everyone on to Macs (we had two folks using PCs). It's been over a year and we have not had a single problem since doing the above. I believe that the PCs weren't an issue, but once the leases were up on those two laptops, I replaced them with the Mac titanium laptops . . . everyone seems happy enough!

Jody Breiland McClenahan Bruer

Hi Jennifer -

We've had that problem in the past after a power outage or other event. After the database recovery & reindexing, we have always been advised by C&P Helpdesk to do an import/export to create a new database (you do not lose any data - it simply creates the "shell" for your database). The only data you may "lose" is corrupted data which is one purpose of the import/export. I have found that by running this import/export utility, we avoid the repetitive database corruptions. Just doing a recover/reindex will not completely resolve the problem. You may want to ask C&P Helpdesk about doing this.

Cheryl Brunner Office Manager Crowl, Montgomery and Clark

Catherine,

There's nothing in the program itself that affects how data is managed on your server; that's a function of the C&P Player and the file server itself. So installing the Classic 5.0.4 wouldn't have caused problems with the database -- it's just a coincidence.

Of all of the files used by Clients & Profits, the Costs file is one of the most likely to get damaged because it is used for time entries, job costs, expenses, and internal charges. It's usually one of your largest files in the databases, which means it takes more space on your server's hard disk. Any problems you may be having with the hardware will have a greater affect on the largest files, which includes Costs.

The C&P Helpdesk can help you troubleshoot the problem and find a solution. I can tell you from experience that unless you find the root cause of the problem (which can be hard to do sometimes), it'll never get better. If you're curious, the Clients & Profits Database Guide explain how and why this happens and what you can do to fix it and then prevent it from happening again:

http://www.cnp-x.com/support/database_guide.html

Mark Robillard Developer, Clients & Profits

We've been having database corruption problems since upgrading to OSX (Panther on the workstations and Jaguar on the Server.) We're an all Mac house, with a G4 Server which has been running OSX since October or so of last year. We didn't start having these problems until the workstations and subsequent applications were migrated to OSX.

One of the upgrades was to Retrospect 6.0 with which I had many, MANY problems. Sometimes, when the backup failed during execution, my database would end up being corrupted. I'd have to run Disk Utility and Disk Warrior on the Server, then repair the C&P database. Everything would be fine until the next backup failure. Not all backup failures resulted in database corruption, but all incidents of database corruption were after backup failures.

Things have settled down with all the latest software updates from Apple for the Server and Retrospect and a firmware update for my VXA drive. Honestly, I don't know if there's a connection, but may be worth looking into. If you're using Retrospect, you might want to check out their site for information and updates.

Good luck Ann Marie -- Ann Marie Tullo | Financial Coordinator Ph.D http://www.phdla.com

Donna,

Did this problem just start happening, or has it happened for a long time?

The totals on the job summaries are updated when time cards and time sheets are posted (or when they are approved, if you have that option enabled). Could it be that users are getting the "lock" cursor when they post then shutting down their computers because they think it's frozen? If so, that would keep the time from posting to the jobs.

The C&P Helpdesk can help you troubleshoot the problem, so I'd give them a call. They'll walk you through some possible scenarios.

Mark Robillard Developer, Clients & Profits

When we first came on board with Clients and Profits we had major database problems. We found out that our problems were always connected to the hardware of our network or computers. We found a very reliable company that was very knowledgeable about networks to help solve our problem. The things that caused problem were bad wiring on the network so we replaced the bad wires. Then I had a computer that had a power supply that was going out which was causing database problems. I found out that if computers are more than 2-3 years old, they can be the cause of database errors. Our networking company also installed a new Cisco switch for the network. They could plug into the switch for a week at a time and locate the computer that was causing the damage. Needless to say by the time we got through this whole process we had upgraded all of our computers. The final hurdle was an incorrect setting on the switch.

After a year of daily database problems and making many costly changes we have had no database problems in one and a half years. For us it was a hardware issue. We had to be very proactive with out networking company to get them to come up with solutions to our problem. We are also in an all Mac environment running OS 10.3.3.

If you have any question please contact me. I know your pain.

Kathy Business Manager

We are running C&P 5.04 on a dedicated G4 with no problems for over 4 years...The only database corruption that occurred was when we had PCs accessing the database while PCMacLan was installed. We have since taken all the PC's off the C&P data entry and have not had any problem. I also run Directory and Disk defragamentation programs from Alsoft (Disk Warrior and Plus Optimizer) on a regular basis.

Renee Gluckman IT The Harrington Group

We found that if people are using the mouse to click through the fields in there time sheet that time is not applied to the jobs correctly. We have a new saying that goes" The Tab is your friend" you must tab thought the fields.

Rob Holmbeck Lanphier Associates, Inc.

Rob,

This is definitely true. Clients & Profits was designed a long time ago with interactive error-checking; each field is validated as you tab out of it, rather than wait until the user clicks "Save" then report all of the errors. Pressing tab also looks up information as you go (for example, copying the job type's spec sheet to a new job) which saves time and makes your work more accurate.

Mousing around instead of tabbing wouldn't cause database damage since it's not affecting the structure of the data (unlike restarting your computer after getting a "lock" cursor, which can). Also, most windows check again for errors when the user clicks "Save". In the old days, this slowed down the program so was used sparingly. But now that computers are so fast, Clients & Profits double-checks for errors almost everywhere when a record is saved.

Mark Robillard Developer, Clients & Profits

We have been using clients and profits for almost a year now. In that time we have had nothing but problems with the database. Just recently we have put C&P on its' own dedicated server, run the repair/reindex and export/import numerous times and finally thought we had the problem under control. Just today I've discovered that problems are still there. The system seems to generate entries of it's own. This time it is 8 pages of one repeated entry. We have not had any system crashes, power outages or anything like that. And we know that this was not an input error. Has anyone else experienced similar problems?

Caren Radford Bookkeeper/Office Manager M Space