Configure SQL Server Agent XPs


To Enable Agent XPs

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

To disable Agent XPs

sp_configure ‘show advanced options’, 0;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 0;

GO

RECONFIGURE

GO

Agent XPs Component is turend off


Cannot show requested dialog. Additional information: unable to execute requested command. ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure.

 

 

 

 

 

 

 

 

 

 

 

 

 

Actions Required to resolve this Error

 

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

divide by zero during and tinyint overflow


— SET ARITHABORT AND SET ANSI_WARNINGS
——————————————————————————-
— Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
   a TINYINT,
   b TINYINT
);
CREATE TABLE t2 (
   a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO

PRINT ‘*** SET ARITHABORT ON; SET ANSI_WARNINGS ON’
GO
— SET ARITHABORT ON and  AND SET ANSI_WARN ON AND testing.
SET ARITHABORT ON
SET ANSI_WARNINGS ON
GO
PRINT ‘*** Testing divide by zero during SELECT’;
GO

SELECT a / b AS ab
FROM t1;
GO

PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO

PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data – should be no data’;
GO
SELECT *
FROM t2;
GO

— Truncate table t2.
TRUNCATE TABLE t2;
GO

— SET ARITHABORT OFF AND SET ANSI_WARNINGS OFF and testing.
PRINT ‘*** SET ARITHABORT OFF;SET ANSI_WARNINGS OFF;’
GO
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
GO

— This works properly.
PRINT ‘*** Testing divide by zero during SELECT’;
GO
SELECT a / b AS ab 
FROM t1;
GO

— This works as if SET ARITHABORT was ON.
PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO
PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data’;
GO
SELECT *
FROM t2;
GO

— Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO

using the SET ARITHABORT AND SET ANSI_WARNINGS


Terminates a query when an overflow or divide-by-zero error occurs during query execution.

SET ARITHABORT  [ON/OFF]

SET ANSI_WARN [ON/OFF]

i will explain very simple example to know what is the benefits from make this option is ON or OFF

— SET ARITHABORT AND SET ANSI_WARNINGS
——————————————————————————-
— Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
   a TINYINT,
   b TINYINT
);
CREATE TABLE t2 (
   a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO

PRINT ‘*** SET ARITHABORT ON; SET ANSI_WARNINGS ON’
GO
— SET ARITHABORT ON and  AND SET ANSI_WARN ON AND testing.
SET ARITHABORT ON
SET ANSI_WARNINGS ON
GO
PRINT ‘*** Testing divide by zero during SELECT’;
GO

SELECT a / b AS ab
FROM t1;
GO

PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO

PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data – should be no data’;
GO
SELECT *
FROM t2;
GO

— Truncate table t2.
TRUNCATE TABLE t2;
GO

— SET ARITHABORT OFF AND SET ANSI_WARNINGS OFF and testing.
PRINT ‘*** SET ARITHABORT OFF;SET ANSI_WARNINGS OFF;’
GO
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
GO

— This works properly.
PRINT ‘*** Testing divide by zero during SELECT’;
GO
SELECT a / b AS ab 
FROM t1;
GO

— This works as if SET ARITHABORT was ON.
PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO
PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data’;
GO
SELECT *
FROM t2;
GO

— Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO

Property ErrorLogFile is not available for JobServer


 

 

 

 

 

 

 

 

This Error Show to me when i want to see the Properties of SQL Server Agent to my instance

 troubleshoot this Error

  1. go to Start >>>> Run >>>>> Write (Regedit)
  2. HKEY_LOCAL_MACHINE >>>>SOFTWARE>>>>MICROSOFT>>>>MICROSOFT SQL SERVER >>>> (YOUR INSTANCE NAME)>>>>SQLSERVERAGENT >>>>double click on SQLSERVERAGENT
  3. search in the right list on file name (ErrorLogFile) if you found it right click >>>>> Modfiy >>>>> Check the path in ValueData is true this path
  4. if you not found this file (ErrorLogFile) 
  5. right click on SQLSERVERAGENT >>>>>New >>>> String Value
  6. Rename the file will Create to (ErrorLogFile)
  7. thengo to C: Drive >>>> Programme File >>>>Microsoft SQL Server >>>> (Your Instance Name) >>>>MSSQL >>>>Log >>>>SQLAGENT.OUT
  8. then take this path and go to the file (ErrorLogFile) in the reigistration
  9. then right click >>>> Modify >>>> put the path in Value Data . then ok
  10. try now to open your SQL AGENT Properties .