Solved

Error: Cannot alter 'col_refs' because it is 'ntext'

Posted on 2006-07-07
11
720 Views
Last Modified: 2008-03-04
I am running my company's utility that is upgrading my database.  On one of the steps, the utility fails saying that it cannot alter column because it is ntext.  I tried manually changing this datatype and rerunning the utility only to face the same error.  Also noted that the rowsize exceeds the maximum number of bytes. An insert or update will fail if the resulting row length exceeds 8060 bytes.  I have run this utility before with no problems and had it change the table datatypes on the same table.  Any ideas greatly appreciated.
0
Comment
Question by:mrmecho
  • 6
  • 3
  • 2
11 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 17061928
Hi mrmecho,

The data type isn't the problem.  The problem is that SQL Server won't allow the row to exceed 8060 bytes.  You are updating data in the row, so that the new row would exceed that limit.  There are many posts about that here:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20396370.html
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21670225.html

Or from elsewhere:

http://www.sqlservercentral.com/columnists/sjones/pagesize.asp

Regards,
John
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17062065
>> I tried manually changing this datatype and rerunning the utility only to face the same error. <<
Post the structure of the table, how you "tried manually changing this datatype" and the resulting error message.
0
 

Author Comment

by:mrmecho
ID: 17062802
Thanks for the update.  The only thing is there is no data in the table.  I used a truncate table mailboxes; and am still receiving max row byte errors.  Any ideas??
0
 

Author Comment

by:mrmecho
ID: 17062827
The exact error to the comment above is:  cannot alter column 'discard_addrs' because it is 'text'.  I believe text is being changed to ntext in the upgrade process.   Total row size is 20871 which I understand in regards to exceeding 8060, but there is no data, so is this just a warning??  The error breaks the utility from running and finishing currently.  Thanks!
0
 

Author Comment

by:mrmecho
ID: 17062893
TABLE mailboxes:

 Column_name          Type      Computed     Length     Prec     Scale     Nullable     TrimTrailingBlanks     FixedLenNullInSource     Collation                    
 -------------------  --------  -----------  ---------  -------  --------  -----------  ---------------------  -----------------------  ----------------------------
 mailbox_id           smallint  no           2          5        0         no           (n/a)                  (n/a)                    (null)                      
 interface_id         smallint  no           2          5        0         no           (n/a)                  (n/a)                    (null)                      
 name                 varchar   no           40                            no           no                     no                       SQL_Latin1_General_CP1_CI_AS
 pop_server           varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 pop_account          varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 pop_passwd           varchar   no           20                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 reply_to             varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 discard_addrs        text      no           16                            yes          (n/a)                  (n/a)                    SQL_Latin1_General_CP1_CI_AS
 discard_types        text      no           16                            yes          (n/a)                  (n/a)                    SQL_Latin1_General_CP1_CI_AS
 fa_enabled           tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 fa_size              int       no           4          10       0         no           (n/a)                  (n/a)                    (null)                      
 desc_size            int       no           4          10       0         no           (n/a)                  (n/a)                    (null)                      
 force_reply_between  smallint  no           2          5        0         no           (n/a)                  (n/a)                    (null)                      
 sa_max_suggestions   smallint  no           2          5        0         no           (n/a)                  (n/a)                    (null)                      
 save_bulk_msgs       tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 save_returned_msgs   tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 default_mbox         tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 from_address         varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 display_name         varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 enabled              tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 pull_limit           int       no           4          10       0         no           (n/a)                  (n/a)                    (null)                      
 discard_hdr          varchar   no           4000                          yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 discard_body         varchar   no           4000                          yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 discard_files        varchar   no           4000                          yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 discard_subj         varchar   no           4000                          yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 vis                  int       no           4          10       0         yes          (n/a)                  (n/a)                    (null)                      
 seq                  int       no           4          10       0         yes          (n/a)                  (n/a)                    (null)                      
 reject_addr          varchar   no           80                            yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 ar_filter_type       tinyint   no           1          3        0         no           (n/a)                  (n/a)                    (null)                      
 priv_key             varchar   no           4000                          yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 key_passwd           varchar   no           160                           yes          no                     no                       SQL_Latin1_General_CP1_CI_AS
 ssl_method           tinyint   no           1          3        0         yes          (n/a)                  (n/a)                    (null)                      
 ssl_trust            tinyint   no           1          3        0         yes          (n/a)                  (n/a)                    (null)                      
 smime_trust          tinyint   no           1          3        0         yes          (n/a)                  (n/a)                    (null)                      
 tmp_hold             ntext     no           16                            yes          (n/a)                  (n/a)                    Latin1_General_CI_AI_KS_WS  
 cert                 ntext     no           16                            yes          (n/a)                  (n/a)                    Latin1_General_CI_AI_KS_WS  

 36 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 438/ms]
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 25

Expert Comment

by:jrb1
ID: 17062999
There is a warning when you create a table with a row greater than 8060 bytes, but I don't think it should cause you a problem.  What is the column being altered to?
0
 

Author Comment

by:mrmecho
ID: 17069527
The column is being changed from text to ntext.
0
 

Author Comment

by:mrmecho
ID: 17070350
I think the first error is really a warning about the row btye limitation.  The real error is the following:

More exceptions ... [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter column 'discard_addrs' because it is 'text'.

If there is no data in the mailboxes table (used truncate and delete operations on table), why is it not possible to update the field from text to ntext??
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17070365
Simply put you cannot use ALTER TABLE to change a text column.  Period.

Since you have no data, you should drop the table and create a new table with correct structure.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 17070465
acperkins is correct

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

ALTER TABLE

ALTER COLUMN

The altered column cannot be:

A column with a text, image, ntext, or timestamp data type.
========
You can do this:

ALTER TABLE mailboxes DROP COLUMN discard_addrs
ALTER TABLE mailboxes ADD discard_addrs NTEXT
0
 

Author Comment

by:mrmecho
ID: 17070525
Thanks for the info guys.  I reran our company's product from earlier versions and saw this field (discard_addrs) was set as varchar(4000) not text.  In other words, the customer's data has become corrupted, and I'm not sure at what point that happened.  The installation should be upgrading from varchar(4000) to ntext.  I will have to manually manipulate the datatype on this column.  Thanks for all your help.  -Mark
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now