Category: Configuration

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

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