Backup SQL Server Database using FoxPro SQL Pass Through

[carolx]

Hi Forum good evening.

Please help me figure out what is happening here.

If I run the database backup Stored Procedure in the query window using: exec [Sundries].[MCRevenueBackupSP] "C:\Marshall\" it completes ok.

If I run the FoxPro SQL Pass Through it doesn't complete successfully. The return value is -1

See file attached with the code.

 

[Mike Lewis]

Yo are correctly testing for a return value of -1 from SQLEXEC(). But you also need to check the actual error number returned from SQL Server. To do that, call AERROR() when lnReturn = -1. That will produce an error array. The array should contain 1526 in its first element, to indicate a remote data error. The third and fifth elements respectively will then contain the SQL Server error message and the SQL server error number. That information will help you identify the cause of the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

You make me wonder whether SQLEXEC does not work here as it works synchronously and would hit a timeout, this returning -1 as the server didn't yet finish and reports back success, but the backup actually does not fail.

Anyway, I'd recommend not doing backups from within the application, as y backup only works nicely without any connection, the SSMS query window does work while it also has a connection, so I'm not 100% sure, but in your application triggered backup you'll usually have several connections, that differs. I have maintained application which did the backup as last step when quitting. That worked, but even in that case I changed it to doing backups from task schedulare programmed directly at the server, which works better. My 2 ¢.

Chriss

 

[Mike Lewis]

Do you have a good reason for initiating the backup from within your VFP code? Have you thought about doing the whole thing within SQL Server, perhaps by scheduling your backups via SQL Server Agent? That would be a matter of specifying your BACKUP DATABASE command as a job, and then setting a schedule for the job to be executed.

I've never actually done this, and there might be more to it than described here, but it would be worth considering.

(And it won't work if you are using SQL Server Express, as that doesn't support jobs.)

But, in any case, I think you should first try and identify the original error in your VFP code, using AERROR() as described above.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

you should first try and identify the original error in your VFP code, using AERROR() as described above.

I second that. You might find a solution to keep the backup functionality within your application.

Anyway, as I already recommended using scheduled tasks or as Mike suggests SQL Server Agent, you get the advantage of doing a backup over night in a state without user connections, which surely is preferrable.

The reason I used and recommended scheduled tasks and not an SQL Server Agent job was for this being more than just a backup and being implemented in VFP, too, but true, SQL Server is a self contained solution that includes backup planning and other tasks. Also SQL Server Agent jobs can do more than backups, but are obviously limited to the database. Wait and see, the express versions gained a lot of the things that initially only were available in Enterprise versions. But you don't need to wait for BACKUPS in themselves, and Task Scheduler is free to use with any EXE or also batch file, which often is sufficient, too.

Chriss

 

[carolx]

It was a database permission problem. I included doing backups from task scheduling. Thanks for the suggestions. Carolx

 

[Mike Lewis]

Good to see you have solved the problem, Carolx, and thanks for letting us know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads