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'.

Following edit: try EXEC msdb.dbo.sp_add_job @job_name=N'test' — Ed Harper, Aug 28 '09 at 10:07
The EXECUTE permission was denied on the object 'sp_add_job', database 'msdb', schema 'dbo'. I'll try to force permissions onto myself — CheeseConQueso, Aug 28 '09 at 14:12

score 11 · Accepted Answer · edited Aug 28 '15 at 20:50

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.

you could probably get around it by using that code block on them — CheeseConQueso, Aug 26 '09 at 15:04
You'll also need to add a job server after this - See Ed's answer below for a link. — Jamie Love, May 03 '10 at 23:31

score 1 · Answer 2 · answered Aug 25 '09 at 21:26

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.

answered Aug 25 '09 at 21:26

Ed Harper

21,127
4
54
80

CheeseWithCheese could also write a small Windows Script Host script to perform this operation - VBScript supports ADO - and then use Windows to schedule this script. – Chris Judge Aug 25 '09 at 22:27
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) – CheeseConQueso Aug 26 '09 at 15:03
I'll look for other directions... something about that msdn site really doesn't get me excited – CheeseConQueso Aug 26 '09 at 15:08
I think they removed that stored proc..... Could not find stored procedure 'dbo.sp_add_job'. using the command: EXEC dbo.sp_add_job @job_name=N'test' – CheeseConQueso Aug 26 '09 at 15:14
You need to run the job creation sps in the msdb database, or refer to them with three-part syntax - msdb.dbo.sp_add_job. – Ed Harper Aug 26 '09 at 21:39

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

2 Answers2