SET ANSI_WARNINGS (T-SQL)

Specifies SQL-92 standard behavior for several error conditions.

Syntax

SET ANSI_WARNINGS {ON | OFF}

Remarks

SET ANSI_WARNINGS affects these conditions:

• When ON, if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) a warning message is generated. When OFF, no warning is issued.
• When ON, divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated. When OFF, divide-by-zero and arithmetic overflow errors cause null values to be returned. A divide-by-zero or arithmetic overflow error causes null values to be returned occurs if an INSERT or UPDATE is attempted on a character, Unicode, or binary column in which the length of a new value exceeds the maximum size of the column. If SET ANSI_WARNINGS is ON, the INSERT or UPDATE is canceled as specified by the SQL-92 standard. Trailing blanks are ignored for character columns and trailing nulls are ignored for binary columns. When OFF, data is truncated to the size of the column and the statement succeeds.

The user options option of sp_configure can be used to set the default setting for ANSI_WARNINGS for all connections to the server. For more information, see sp_configure or Setting Configuration Options.

Microsoft® SQL Server™ includes the ANSI warnings database option, which is equivalent to SET ANSI_WARNINGS. When SET ANSI_WARNINGS is ON, errors or warnings are raised in divide-by-zero, string too large for database column and other similar errors. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised. The default value in the model database for SET ANSI_WARNINGS is OFF. If not specified, the setting of ANSI warnings applies. If SET ANSI_WARNINGS is OFF, SQL Server uses the ANSI warnings setting of sp_dboption. For more information, see sp_dboption or Setting Database Options.

ANSI_WARNINGS should be set to ON for executing distributed queries.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set ANSI_WARNINGS to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties set in the application before connecting. SET ANSI_WARNINGS defaults to OFF for connections from DB-Library applications.

When SET ANSI_DEFAULTS is ON, SET ANSI_WARNINGS is enabled.

The setting of SET ANSI_WARNINGS is set at execute or run time and not at parse time.

If either SET ARITHABORT or SET ARITHIGNORE is OFF and SET ANSI_WARNINGS is ON, SQL Server still returns an error message when encountering divide-by-zero or overflow errors.

Permissions

SET ANSI_WARNINGS permissions default to all users.

Examples

This example demonstrates the three situations mentioned above with the SET ANSI_WARNINGS to ON and OFF.

USE pubs

GO

CREATE TABLE T1 ( a int, b int NULL, c varchar(20) )

GO

SET NOCOUNT ON

GO

INSERT INTO T1 VALUES (1, NULL, '')

INSERT INTO T1 VALUES (1, 0, '')

INSERT INTO T1 VALUES (2, 1, '')

INSERT INTO T1 VALUES (2, 2, '')

GO

SET NOCOUNT OFF

GO

PRINT '**** Setting ANSI_WARNINGS ON'

GO

SET ANSI_WARNINGS ON

GO

PRINT 'Testing NULL in aggregate'

GO

SELECT a, SUM(b) FROM T1 GROUP BY a

GO

PRINT 'Testing String Overflow in INSERT'

GO

INSERT INTO T1 VALUES (3, 3, 'Text string longer than 20 characters')

GO

PRINT 'Testing Divide by zero'

GO

SELECT a/b FROM T1

GO

PRINT '**** Setting ANSI_WARNINGS OFF'

GO

SET ANSI_WARNINGS OFF

GO

PRINT 'Testing NULL in aggregate'

GO

SELECT a, SUM(b) FROM T1 GROUP BY a

GO

PRINT 'Testing String Overflow in INSERT'

GO

INSERT INTO T1 VALUES (4, 4, 'Text string longer than 20 characters')

GO

PRINT 'Testing Divide by zero'

GO

SELECT a/b FROM T1

GO

DROP TABLE T1

GO

See Also