How do I create a SQL Server agent without using a GUI?

Question

I am using Angel LMS and its built on a SQL Server platform. I believe its 2005, but not 100% sure about that.

Anyway, maybe my pseudo-code will shed light on that answer. Also single quotes must be used for strings and the concatenate character is +.

I need to have something run 2-5 times a day (the frequency is not determined yet, but you get the point). Here's the transaction block/pseudo-code:

BEGIN TRANSACTION
BEGIN TRY

<select statement>
<update statement>
<delete statement>
<insert statement>

COMMIT TRANSACTION
END TRY
BEGIN CATCH
        ROLLBACK TRANSACTION
        DECLARE @Msg NVARCHAR(MAX)  
        SELECT @Msg=ERROR_MESSAGE() 
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG
END CATCH

My only access to the database, is a text box that runs sql commands. I can create and drop tables, run transactions blocks, and obviously select/insert/update/delete. I can't find any commands to create an agent, but only the steps if you are using the Enterprise Manager or whatever the GUI is called.

Also, some reference on how to manipulate the timing schedule of the agent would help out. When I test it, I want to set it to run every fifteen minutes or so.

---

EDIT
EXEC dbo.sp_add_job @job_name=N'test' returned the error
Could not find stored procedure 'dbo.sp_add_job'.

Answer 1

If you need to create a SQL job in SQL Server Agent (assuming you have rights), you have to create the following:

1) The job itself 2) A step in the job to run the SQL code 3) A schedule of when to run it.

The following stored procedures are necessary to do this (working example):

BEGIN TRY
    BEGIN TRAN

    DECLARE @jobId BINARY(16)

    --Add job
    EXEC msdb.dbo.sp_add_job @job_name=N'Job Name', @job_id = @jobId OUTPUT

    --Add step to job
    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Do SQL Stuff', 
            @step_id=1, 
            @subsystem=N'TSQL', 
            @command=N'SELECT ''Hello, I am a query'';', 
            @database_name=N'DB_Name', 
            @flags=0

    --Add schedule to job
    EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Mon-Fri 6:00AM to 7:00PM, every hour', 
            @freq_type=8, 
            @freq_interval=62, 
            @freq_subday_type=8, 
            @freq_subday_interval=1, 
            @freq_relative_interval=0, 
            @freq_recurrence_factor=1, 
            @active_start_date=20090403, 
            @active_end_date=99991231, 
            @active_start_time=60000, 
            @active_end_time=190000

    COMMIT TRAN
END TRY
BEGIN CATCH
    SELECT ERROR_Message(), ERROR_Line();
    ROLLBACK TRAN
END CATCH

As for the sprocs themselves, you'll want to check out the syntax here:

sp_add_job

sp_add_jobstep

sp_add_jobschedule

It's a little tricky, but that should get you going.