I'm having to import data from an AS400 IBM Mainframe and do nightly exports from Microsoft SQL Server 2000 back to the AS400 IBM Mainframe. I was wondering if there was a way to schedule a stored procedure to run a job at a specific time. More importantly if there was to bring the database down for this maintenance, and run the job. Once the morning import has occurred, then have the database come back online. I would also need to format the data file and export it as a text delimited file and upload it to the mainframe. I know this is a multi-part task I'm talking about. I was wondering if there was any way to automate it any bit.

There are a lot of things you're asking for...but I would recommend the following:

1) Look at the JOBS features in Enterprise Manager. You can execute a stored proc on a nightly schedule, with the following command:

exec lsp_some_proc;

Make sure that you have the MS SQL Agent automatically running or your scheduled jobs will crash.

2) Look into running batch exports (BCP utility)...here is an article that may help:

http://www.windowsitlibrary.com/Content/77/14/1.html#1

The beauty of running the BCP utility are the following:

1) Avoids table locks while exporting/importing data that can occur with stored procs and other processes.

2) You can format the output into a variety of formats to accomodate backward compatibility and other application servers or mainframes.

Hopefully, this will get you started.
========================================
Buh Bye!

Cheers,
Me