I need to schedule an update of SQL Server table with the data from Visual FoxPro application.

What is the best way to do it?

I'm trying to do it with SQL stored procedure.
Here are my questions:
1. How I can link FoxPro table to SQL Server?
2. Is it possible to schedule stored procedure to automatically run at certain intervals (let's say every 3 hours)?

We're currently using SQL 7.0, but we'll switch to SQL 2000 soon.

Thanks a lot,
Sofya

2) You can schedule that to run every 3 hrs. That is possible.
1) But I haven't tried my hands on linking a foxpro table to sql server. You may try googling on that.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks for your replay Vijay G.
How I can schedule a stored procedure to run every 3 hours?

Thanks again.
Sofya

Hi Sofya,

On Enterprise Manager, Expand the server >> Management >> SQL server Agent >> Jobs

Then on the right-hand-side pane, right click to add a new job, where you got to switch to Schedules tab, click New Schedule button, give that a name, and ensure that Recurring radio button is selected, click Change button, select Daily radio button within Occurs group, select "Occurs every" within Daily frequency group, make that 3 hours. Then press OK on all popped up windows.

That should let you schedule the job to run every 3 hrs.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks!

Two questions:
1. Where in the 'New Jobs' I can specify a stored proc name that needs to be run?
2. By default 'Target local server' is checked, should and how I can change it to 'Target multiply servers' in order to update tables from local server with data from linked (FoxPro)server?

Thanks,

Sofya

1) Yes, you got to create a step and there specify the stored proc that would do that job.

2) IMO, you should let the stored proc do that job of updating tables, If I understood that right, you are looking for data from foxpro table and updating the same to table on local sql server. The stored proc should be coded in such a way it queries the foxpro table and updates the sql table. That doesn't have anything to do with changing the "target local server" to something else.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Great Thanks Vijay G!
I'll try it tomorrow. I'll ask you if I'd got more questions.

Sofya