If you’re creating a SQL Server Agent job for the first time, you might find that you need to enable the SQL Server Agent extended stored procedures (SQL Server Agent XPs) first.

When you use the SQL Server Management Studio (SSMS) tool to start the SQL Server Agent service, these extended stored procedures are enabled automatically.

When you use T-SQL, you can enable the SQL Server Agent XPs with the sp_configure stored procedure.

Example

First, run the following code to show the advanced options

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

Result:

Started executing query at Line 18
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Started executing query at Line 20
Commands completed successfully.
Total execution time: 00:00:00.126

After the first line ran, it advised us to run RECONFIGURE, which we did.

Check the Agent XPs Current Setting

First, let’s check the current setting for the SQL Server Agent XPs.

EXEC SP_CONFIGURE 'Agent XPs';

Result:

 +-----------+-----------+-----------+----------------+-------------+
 | name      | minimum   | maximum   | config_value   | run_value   |
 |-----------+-----------+-----------+----------------+-------------|
 | Agent XPs | 0         | 1         | 0              | 0           |
 +-----------+-----------+-----------+----------------+-------------+ 

The config_value and run_value settings are 0, which means that it’s not enabled. We want these to be 1.

The possible values are:

Value	Description
0	SQL Server Agent extended stored procedures are not available (the default)
1	SQL Server Agent extended stored procedures are available.

Enable Agent XPs

Here’s how to enable the Agent XPs.

EXEC SP_CONFIGURE 'Agent XPs', 1;
GO
RECONFIGURE;
GO

Result:

Started executing query at Line 23
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

Started executing query at Line 25
Commands completed successfully.
Total execution time: 00:00:00.142

This completed successfully.

Re-check the Agent XPs Current Setting

Now let’s check the setting again.

EXEC SP_CONFIGURE 'Agent XPs';

Result:

 +-----------+-----------+-----------+----------------+-------------+
 | name      | minimum   | maximum   | config_value   | run_value   |
 |-----------+-----------+-----------+----------------+-------------|
 | Agent XPs | 0         | 1         | 1              | 1           |
 +-----------+-----------+-----------+----------------+-------------+ 

We can now see that config_value and run_value are 1, which is what we want.

Hide Advanced Options

Once finished, you might want to hide the advanced options again.

EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO