ALTER TABLE and ANSI_WARNINGS

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

0
cbrichards
5/5/2010 3:22:20 PM
sqlserver.server 1327 articles. 0 followers. Follow

5 Replies
1603 Views

Similar Articles

[PageSpeed] 3

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@uwe> wrote in message 
news:a78e0f670ee0b@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
> 
0
Kalen
5/5/2010 4:04:37 PM
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

0
cbrichards
5/5/2010 5:38:28 PM
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@uwe> wrote in message 
news:a78f3f7ff8a52@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
> 


0
Uri
5/5/2010 5:50:30 PM
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@uwe> wrote in message 
news:a78f3f7ff8a52@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
> 
0
Kalen
5/5/2010 6:19:13 PM
I have tested with both ANSI_WARNINGS ON and OFF. If I expand the Views
folder, there are not any views on this table, let alone an indexed view.
There are not any computed columns on the table. There is a unique identifier
data type on one column, but all the other columns are of int or varchar.
There are not any filtered indexes or spacial indexes, either.

Kalen Delaney wrote:
>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)
>> 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 
>[quoted text clipped - 18 lines]
>>>>
>>>> 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

0
cbrichards
5/5/2010 10:25:13 PM
Reply:

Similar Artilces:

Altering font sizes
I am currently trying to resize the font throughout the whole of a Publisher document, but am having significant trouble acheiving it. Due to the nature of the document, it has well over 50 separate text boxes. I discovered recently that the document (a diagram) would have to be extended, so was intending to shrink it all slightly to fit in the extra parts. Having selected it all (Ctrl-A), grouped it, and shrinking it slightly, the text no longer fits within many of the text boxes. When i went to change the font size, all the font parts within the formatting toolbar were grayed out, a...

pivot table #6
I have been tasked with putting together a pivot table for my group that will highlight if a persons vacation falls within blackout dates. Right now I have a list of names and a start and end date for there vacations. I also have a list of blackout dates set up with start and end dates. Not sure about where to start or if this can be done? Below is what I have so far Tab 1 A B C 1 John Doe 10/1/08 10/8/08 2 Jane Doe 1/2/08 1/9/08 Tab 2 A B C 1 blackout A 1/7/08 1/20/08 2 blackout B 9/28/08 ...

Pivot Table Calculation
I am trying to calculate a percent from a total in my pivot table. I can get the results if I use % of Total, this is the total of the whole report. Is there a way I can use a calculation on a subtotal? The end result what percent of Cruise and Tour is for each customer. Example: Customer Name Cruise $ Tour $ Subtotal Air Car Subtotal Total Customer1 (Repeat) --- Message posted from http://www.ExcelForum.com/ ...

Altering Cursors
Hello, I have some toolbar buttons in my dialog DLL app and two of them are zoom-in and zoom-out. Is there an MFC class like CWaitCursor that can display a zoom-in/zoom-out cursor? Thank you so much. Victor. You will have to change the cursor yourself. Here is an example of the how to change cursors, load the cursor in the m_CursorHand variable: (It's a comment for how to solve a problem of another problem, but it does explain changing cursors nicely) http://www.codeguru.com/Cpp/controls/buttonctrl/comments.php/c2069/?thread=26628 AliR. "victorsk" <victorsk@discussio...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss´┐Ż:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

container for tables & queries in VB.NET 2008
Hello. Is there any container on VB.NET 2008, so I can drop queries & tables objects on it? Thanks :) Try a Dataset. You can select it from the Add menu under "Dataset" (.xsd file) or create it in code Dim ds As New Dataset Rich *** Sent via Developersdex http://www.developersdex.com *** Can I drop mySqlConnection or other connection objects to such a container, or similar ? Thanks :) "Rich P" <rpng123@aol.com> wrote in message news:uBa63pB0KHA.2512@TK2MSFTNGP05.phx.gbl... > Try a Dataset. You can select it from the Add menu ...

table of contents
The whole tab thing just works like it's broken in Pub 2000. No matter how many times I reset the tabs for the table of contents, it goes back to mish mash when I open it.. I then have to reset each line individually because it's not smart enough to do the whole page. The last line doesn't have a chapter number so it won't stay in line with the others at the right edge. It's a real pain in the neck. After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Asussertown <DROPsussertown@adelphia.net>... > The whole tab thing just works like it'...

Linking Tables 04-30-07
Ok, I failed to get my tables combined satisfactorily. Is there a way to link tables within a database so that I don't have to enter the same information twice? I have 4 common fields in both databases. Fields for Table (Maps) are as follows: Drawer, Name, TMS Number, Date, Job Number, Location, Subdivision, Block, Lot, Tract, Area, Surveyor, Revision, Disk. Fields for Table (DCA) are as flollows: Disk, Name, Job Number, Description, Date. The Maps table has over 30,000 records, the DCA table has 10,000 records. "KARL DEWEY" <KARLDEWEY@discussions.microsoft.com>...

tables #2
which tables should be deleted for posted trx in all modules. (financial, ap, ar,inventory, fixed assets, sop, pop). thank you .. ...

Data Table #2
Hi I'm going around in circles!! I have the following: Tons Price/Ton Sales 555.00 6,420.00 Prod 627.40 5,482.86 Contr 937.14 6,420.00 937.14 1000 937.1380002 1500 937.1380002 2000 937.1380002 2500 937.1380002 3000 937.1380002 3500 937.1380002 4000 937.1380002 4500 937.1380002 5000 937.1380002 5500 937.1380002 6000 937.1380002 6500 937.1380002 7000 937.1380002 7500 937.1380002 8000 937.1380002 8500 937.1380002 9000 937.1380002 The 937.14 in the table to the right of 6420 is a formula which is 6420-5482.86 The 6420 is a cell reference to the 6420 in the table abov...

Pivot Table Total/Subtotals
Hi all I have data with the following structure (column headings): Category Brand Group M1 M2 M3... M12 where M1 is month 1, M2 is month 2, etc. I would like a pivot table that I can manipulate in different ways to show totals by month, quarter and year. Question is, can I keep the months in columns to do this, or do I need a different row for each month? Thanks in advance Paul Martin Melbourne, Australia Hi, Keep months in column or row and then use the Group feature. To do this, right click on the Pivot table and select Group and choose your grouping options there. When y...

message date/time altered when copying from public folder
I have noticed that if we copy mail messages from public folders to users own mailboxes, the time is off by a few minutes to a few hours. If a message arrives late at night, the copied mail may even have the next day's date. We have mail-enabled public folders, and users copy mail to their inboxes from there, but this date/time change is bothering. Do the headers contain different time stamps, and does Outlook read them differently to the Public folders? Any ideas? Jo On Sun, 1 Oct 2006 15:31:18 +0900, "Jo" <gsc@gol.com> wrote: >I have noticed that if we copy mail...

alter function
Hi, using SQL2005. I want to replace a function and can't because the function is being referenced by an object. I have tried to drop it first and then create the function. This has the same error result. Is there a way to ignore any referenced object and drop a function (and then re-create it). Alternatively any way to ignore referenced object and alter a function. Any ideas or recommendations appreciated :-) Many thanks, Jonathan Hi Havv you created a UDF with SCHEMA BINDING option? "Jonathan" <Jonathan@discussions.microsoft.com> wrote in messag...

Average in Pivot Tables
Hi, I have created a table with the following data student id, exam name, score. I want to create the following pivot table | Exam name 1 | Exam name 2 --------------------------------------- student id1 | Score | Score student id2 | Score | Score student id3 | Score | Score --------------------------------------- Average Average Excel will only let me give the Grand total. Is it possible to get the average. The sum of the score is meaningless. With regards Constantijn Enders If you go to Field settings for your data fields and change S...

Sheet protection code conflicts with Pivot Table "auto refresh"
I received the following code from Debra Dalgleish, to password protect all worksheets, setting certain exceptions for objects, etc. Public Sub ProtectAll() > Const PWORD As String = "mysecretword" > Dim wsSheet As Worksheet > For Each wsSheet In Worksheets > wsSheet.Protect Password:=PWORD, _ > DrawingObjects:=False, _ > AllowFiltering:=True, _ > AllowUsingPivotTables:=True > Next wsSheet > End Sub It works well, except for one thing: when I open the file, I get an error message which emanates fr...

Altered meeting always becomes recurring
We're on an Outlook 2007 / Exchange 2008 setup. Here's what happens: 1. Person sends a New Meeting Request for, say, Monday at 9 AM. Everything works properly and the entry appears as it should on the calendar. 2. Person later decides to change the meeting to, say, Tuesday at 10 AM. So they open the original entry on the calendar (from step #1 above) and changes the date to Tuesday and the time to 10 AM, then sends the update. 3. The meeting appears in the new location on the calendar OK but it has been changed into a recurring event. I've verified that the r...

How do I put a landscape table into a portrait report
I am writing a report in portait and want to place a table that is in landscape into the report. . . how do I do this? Thanks, See http://word.mvps.org/FAQs/Formatting/LandscapeSection.htm. Although the stated purpose of this article is to explain how to put a portrait page number (or header/footer) on a landscape page, it does start by explaining how to create the required landscape section for your table. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Gwen" <Gwen@discussions.microsoft.com> wrote...

Pivot Tables
I have external data comming in from a database, when i alter the data the pivot table still shows row selections from the old data as well as the new, is there any way to reset the list without removing and adding back in the table. I cant find where in the spreadsheet it stores this information. Steve There are instructions here for clearing old items from the dropdown lists: http://www.contextures.com/xlPivot04.html Steve wrote: > I have external data comming in from a database, when i alter the data the > pivot table still shows row selections from the old data as well ...

Custom Tables
Is their a way to add a custom table to report writer? I am trying to add a field to the check remittance report that is not defined in the great plains table structure. Yes, it is possible with VBA code to add any external data fields on a report. "Steve" wrote: > Is their a way to add a custom table to report writer? I am trying to add a > field to the check remittance report that is not defined in the great plains > table structure. Is it possible to add custom fields from a custom table where their will be more than one line of data through VBA. And if so...

how to drag multiple items to pivot table report at once
I am creating and re-creating a database of numerous products with specifics about each one. When I do the pivot table, is there a way to easily drag all the items into the data field rather than having to sit and individually drag and drop each one? ...

Data Modeling:Lookup table and Main table:establishing relationshi
I am working on creating data model from existing database using MS Visio 2007 Profesional Edition. Existing database is w/o PK-FKs & I am working to create relational DB which enforces RI. I have a lookup table which contains language codes,used by main table. The problem ,I am running into, is that these languagecodes(from lookup table) are used by 3 columns in main table. So, I am wondering how can I enforce PK-FK relationship here. As in... language_code from lookup table is PK and it has to associated w/ column(s) existing in main table. Something like following: Lookup Table ...

Word Table into Excel
When I copy a row of text (divided into a number of columns) from a wor table and paste in Excel - it copies down all information from acros the columns down into ONE column (col A) instead of across int seperate columns. Is there any way I can copy over details from table in word to cells into Excel without writing VB -- Message posted from http://www.ExcelForum.com you can create a table in Word that is the same size as the excel information that you are trying to move and then simply copy and paste (when pasting make sure that you highlight the entire table though.) >-----Origi...

Altering SP
Can GP stored procedures be altered. How safe is that. In general you should not alter procs created by GP. There is a possibility that the proc could be altered in a hotfix or upgrade. You may not notice that the proc has changed till much later when you find that the proc is not working as you had meant it Intead, find a way to call your own proc using vba or a trigger. HS "Asma" <asma_twinmos@hotmail.com> wrote in message news:1183294932.796908.67450@w5g2000hsg.googlegroups.com... > Can GP stored procedures be altered. How safe is that. > ...

Pivot Table inquiry
How can I remove the field length restriction in a pivot table? It looks like it cuts it off at about 150 characters. ...

How to base a pivot table on another pivot table?
In Excel 2007 I can't work out how to create a pivot table based on another pivot table. I have got a workbook created in Excel 2003 which has: - a first worksheet containing a large table of raw data - a second worksheet containing a minimal pivot table called Base_table which refers to the data in the first worksheet - further pivot tables in the second worksheet and in further worksheets which are based on Base_table This structure is intended to minimise the space used by the pivot tables and to enable a change to the data to be propagated through all the pivot table...