Bogus error messge when altering IDENTITY column when table has a persisted computed column by Erland Sommarskog

Closed

as Fixed

Type:	Bug
ID:	756654
Opened:	8/2/2012 7:12:22 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

If you have a table with an IDENTITY column and a persisted computed column, and you try to change the data type of the IDENTITY column, you get an error message that ANSI_WARNINGS has the wrong setting. If you alter some other column or if there is no PERSISTED column, the command succeds.

I would not say that the this a compelling situation that must work, and it may be by design (restriction) that the command fails. However, the error message is incorrect.

Details (expand)

English

SQL Server 2012 - Developer Edition

SQL Engine

Windows 7 Professional

US English

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
go
CREATE TABLE harald (a int IDENTITY(1,1) NOT NULL, b int, c as 19 PERSISTED)
go
ALTER TABLE harald ALTER COLUMN a bigint NOT NULL
go
DROP TABLE harald

Server: Msg 1934, Level 16, State 1, Line 1
ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Command succeeds or an appropriate error message.

0 attachments

Posted by Microsoft on 2/26/2013 at 7:14 PM

Hi,
Thank you for your feedback. We have investigated the issue and the fix will be available in the next release of SQL Server.
Thanks & Regards,
Pooja Harjani
Program Manager
SQL Server

Microsoft