Tuesday, October 16, 2012

Another error not as it seems

The Error: 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.

 I was attempting to do a simple change
 Alter Table TABLENAME  Alter Column COLUMNNAME BIGINT NOT NULL 

When I got the error above. I was floored, I set ANSI_Warnings every which way but loose. Then I read the error more closely. I dropped the non-clustered index on the computed columns and tried again. No dice. So I looked closer. and decided, well I don't need the computed columns. I dropped the computed columns and then I was able to alter the table. I'm guessing there is a bug in SQL Server 2008 R2 RTM and SP2 (I build a special environment with just this one table). And, yes, I do need a bigint here. This table ran out of ints in less than a year.

1

Labels: , , , , , ,

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home