Looking at your code, not even going to try and fix it.  You are using CASE incorrectly to start.  From your description of the problem, your script (or stored procedure)...

Start by reading about configuration files in BOL.  You can also learn more in the WROX book Professional SQL Server 2005 Integration Services by Brian Knight, et al.

Try this:

SELECT

    dtclass.class_id,

    dtclass.class_date,

    course.course_type_id,

    location.location_id,

    location.display_city,

    location.location_city

FROM

   (select

        class.class_id,

        class.class_date,

        class.course_type_id,

        class.location_id

    from

        dbo.class

    where

        class.class_id in (select top 1 sq.class_id from dbo.class sq where class.course_type_id = sq.course_type_id and...

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?