When backing up Time Matters or another MS SQL Server database, you may get the following error message:
"Error backing up data. Error reported as: Executing SQL directly; no cursor."
I am aware of three possible causes of this error.
1. Error in the Backup Stored Procecure
The first potential problem is an error in the backup stored procedure. For Time Matters, the stored procedure is TM_Backup. It is included in the Time Matters database cannot really have a technical problem since it uses the default settings and default backup destination assigned by the MS SQL Server software. But the default backup destination has a practical problem (one that LexisNexis Time Matters documentation covers).
The Microsoft default backup destination is on the same drive as the database files. That makes sense because Microsoft cannot know what other drive the user might want to use for backups. A backup on the C drive (the default location) is better than no backup at all if you have a database problem. Also, ordinary backup and sync programs that cannot copy the open Time Matters SQL database files can copy the SQL backup (.bak) files to another drive or to the cloud. The practical problem is that if you don't backup the backup (.bak) files and don't backup the C drive, you can lose everything if the C drive crashes, burns, is stolen, etc.
The user or the user's consultant can modify TM_Backup to direct the backup files to another drive. If that is not done properly, the "Executing SQL directly; no cursor" error message will appear when the backup is run.
2. Backup Drive Disconnected or Not Available
The second potential problem is related to the first. If backup stored procedure (TM_Backup) is modified to save backup files to, say, the X: drive, but the X: drive is not available for some reason (disconnected, different drive letter assigned after disconnection and reconnection, USB connection problem, etc.), the "Executing SQL directly; no cursor" error message will appear when the backup is run.
3. Bad Hard Drive Sectors
The third possible cause is bad hard drive sectors or hard drive surface problems. Here is what I encountered on a the server of a law firm client.
A scheduled SQL Server backup failed with the message: “Error backing up data. Error reported as: Executing SQL directly; no cursor.” I ran CheckDB in SQL Studio and identified problems in one of the tables. I detached the .MDF file and attempted to copy it. Windows Copy failed to copy it.
Clearly if Windows cannot read a file, MS SQL Server and Time Matters can't handle it properly either. Corrupted sectors on the hard drive were the culprits. So I created a new data folder for the Time Matters SQL data files and restored the previous day's backup to that folder. Now Time Matters is running just fine. Of course, the firm was at risk until the hard drive was either fixed (with SpinRite - www.grc.com - highly recommended but very time-consuming with today's large drives) or replaced.
-- Wells Anderson