Sounds like a scheduled job to me.  Yes, you can.

I am still not sure what your procedure is supposed to do.  What is the input and what is the expected (not actual) output from the procedure?

Brandie,

Actually, I haven't purchased any of the "test help" books.  I have quite a few of the MS Press books related to SQL Server 2005 which I rely on, but...

FTP in DTS is pull only.  Once you move to SQL2K5 and SSIS, that changes.

What would help even more is sample input data and wha the results of the process should return based on that output.

Sometimes, cursors and dynamic sql make sense for a particular job.  This may be one of those cases.

You should be able to put all that in a script that you run using ftp to rename the file.  We did that many times at my previous employer's.  Unfortunately,...

Also, you can have multiple snapshots on a single source database, so, if you have a snapshot of a database at 9:00, 10:00, and 11:00, and you need to revert...

You can't access the # temp table after your exec (@sqlcmd) because the # temp table no longer exists, it is dropped after the exec completes.  You could change your...

That may be part of the problem.  Do you have the space available on your server to backup directly yo disk and then move the backup file?

Are you backing up to a local disk or across your network to a network share?

How big is your database?

Yes, the SQL Server 2000 and SQL Server 2005 Client tools can coexist on the same system.  I have had no issues with this on any of my systems.

Either that, or just run it at 6:00 AM to import the file created the previous day at 8:00 AM.

Would it be possible to get the DDL for the tables (including indexes) and the query in question?