ALTER TABLE and ANSI_WARNINGS
in [Server Configuration]
|
From: cbrichards via SQLMonster.com on 5 May 2010 11:22 We are running SQL Server 2008 enterprise. I am attempting to change the data type on a column from int to bigint using the following command: ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint When the above command is executed I get the following 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. When I look at the is_ansi_warnings value in sys.Databases, the value is 0. When I run either of the following, and then execute the ALTER TABLE statement, I still get the error. ALTER DATABASE dbAUDIT SET ANSI_WARNINGS ON ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint SET ANSI_WARNINGS ON ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint The is_ansi_warnings value in sys.Databases is now 1. How can I successfully change the column from int to bigint? -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1
From: Kalen Delaney on 5 May 2010 12:04 The database settings for ANSI options are practically useless, as they are overridden by session options when the connection is opened. Look at sys.dm_exec_sessions to see the session settings. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:a78e0f670ee0b(a)uwe... > We are running SQL Server 2008 enterprise. > > I am attempting to change the data type on a column from int to bigint > using > the following command: > > ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint > > When the above command is executed I get the following 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. > > When I look at the is_ansi_warnings value in sys.Databases, the value is > 0. > > When I run either of the following, and then execute the ALTER TABLE > statement, I still get the error. > ALTER DATABASE dbAUDIT SET ANSI_WARNINGS ON > ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint > > SET ANSI_WARNINGS ON > ALTER TABLE dbo.jkAudit ALTER COLUMN jkAudit_UID bigint > > The is_ansi_warnings value in sys.Databases is now 1. > > How can I successfully change the column from int to bigint? > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201005/1 >
From: cbrichards via SQLMonster.com on 5 May 2010 13:38 The catalog view sys.dm_exec_sessions also shows that ANSI_WARNINGS = 1. Therefore, I get the same error message whether ANSI_WARNINGS is ON or OFF, yet the error message says: ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Any idea what needs to be done to get the column data type changed from int to bigint? Kalen Delaney wrote: >The database settings for ANSI options are practically useless, as they are >overridden by session options when the connection is opened. Look at >sys.dm_exec_sessions to see the session settings. >> We are running SQL Server 2008 enterprise. >> >[quoted text clipped - 28 lines] >> >> How can I successfully change the column from int to bigint? -- Message posted via http://www.sqlmonster.com
From: Uri Dimant on 5 May 2010 13:50 Hi > Any idea what needs to be done to get the column data type changed from > int > to bigint? ALTER TABLE tbl ALTER COLUMN col BIGINT Kalen, thanks for that great info, I am just backed home from the second day course "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:a78f3f7ff8a52(a)uwe... > The catalog view sys.dm_exec_sessions also shows that ANSI_WARNINGS = 1. > Therefore, I get the same error message whether ANSI_WARNINGS is ON or > OFF, > yet the error message says: > > ALTER TABLE failed because the following SET options have incorrect > settings: > 'ANSI_WARNINGS'. > > Any idea what needs to be done to get the column data type changed from > int > to bigint? > > Kalen Delaney wrote: >>The database settings for ANSI options are practically useless, as they >>are >>overridden by session options when the connection is opened. Look at >>sys.dm_exec_sessions to see the session settings. >>> We are running SQL Server 2008 enterprise. >>> >>[quoted text clipped - 28 lines] >>> >>> How can I successfully change the column from int to bigint? > > -- > Message posted via http://www.sqlmonster.com >
From: Kalen Delaney on 5 May 2010 14:19
Are you sure you're checking the session where you are issuing the ALTER TABLE? Are you sure you tested the ALTER when the SESSION option was off? You might consider running a trace to see where the setting is getting changed. Is there any chance you have an indexed view or a computed column on the table? (FYI, sys.dm_exec_sessions is not a catalog view, it is a Dynamic Management View) -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "cbrichards via SQLMonster.com" <u3288(a)uwe> wrote in message news:a78f3f7ff8a52(a)uwe... > The catalog view sys.dm_exec_sessions also shows that ANSI_WARNINGS = 1. > Therefore, I get the same error message whether ANSI_WARNINGS is ON or > OFF, > yet the error message says: > > ALTER TABLE failed because the following SET options have incorrect > settings: > 'ANSI_WARNINGS'. > > Any idea what needs to be done to get the column data type changed from > int > to bigint? > > Kalen Delaney wrote: >>The database settings for ANSI options are practically useless, as they >>are >>overridden by session options when the connection is opened. Look at >>sys.dm_exec_sessions to see the session settings. >>> We are running SQL Server 2008 enterprise. >>> >>[quoted text clipped - 28 lines] >>> >>> How can I successfully change the column from int to bigint? > > -- > Message posted via http://www.sqlmonster.com > |