×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Backup SQL Server Database using FoxPro SQL Pass Through

Backup SQL Server Database using FoxPro SQL Pass Through

Backup SQL Server Database using FoxPro SQL Pass Through

(OP)
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.

RE: Backup SQL Server Database using FoxPro SQL Pass Through

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

RE: Backup SQL Server Database using FoxPro SQL Pass Through

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

RE: Backup SQL Server Database using FoxPro SQL Pass Through

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

RE: Backup SQL Server Database using FoxPro SQL Pass Through

Quote (Mike Lewis)

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

RE: Backup SQL Server Database using FoxPro SQL Pass Through

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

RE: Backup SQL Server Database using FoxPro SQL Pass Through

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close