How to create a SQL Server agent without using a graphical interface?

Question

I use Angel LMS and its built on SQL Server platform. I believe that in 2005, but not 100% sure of this.

In any case, maybe my pseudo-code will shed light on this answer. Also, single quotes should be used for strings, and the concatenation character should be +.

I need something to be done 2-5 times a day (the frequency has not yet been determined, but you understand the meaning). 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 the text box in which sql commands are executed. I can create and delete tables, run transaction blocks, and obviously select / insert / update / delete. I cannot find any commands for creating an agent, but only steps if you are using Enterprise Manager or something like a GUI.

In addition, some reference on how to manipulate an agent’s synchronization schedule will help. When I test it, I want to configure it to run every fifteen minutes or so.

---

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

Answer 1

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

1) The work itself 2) The step in the task to run the SQL code 3) The schedule for when to run it.

This requires the following stored procedures (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

sprocs, :

sp_add_job

sp_add_jobstep

sp_add_jobschedule

, .

Answer 2

-, , SQL Server Express Edition, SQL Express SQL Agent.

:

SELECT @@version

.

Express, - , SQLCMD , SQL- SQL Express - "- SQL", . , .

, SQL T-SQL .