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

First off, you need to establish whether the SQL Server is Express Edition, since SQL Express has no SQL Agent functionality.

In your command window, run the following command:

SELECT @@version

The result will include information on the edition.

If you have Express, you will need to find another way to run your commands - possibly using SQLCMD and the windows scheduler, or one of the several third party replacements for the SQL Agent in SQL Express - if you Google "sql express agent", you'll find several. I've no experience with them, so I can't recommend one.

If you have Standard or Enterprise editions, the steps to create SQL Agent jobs through T-SQL can be found here.

Answer 2

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.

P.S. How do you have Stackoverflow ignore formatting of underscores?