Solved

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

Posted on 2006-07-07
11
725 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Removing SCCM 2016 4 51
VMware PVSCSI SQL Server 2016 AlwaysOn 2 36
Using a SUBQUERY for the set variable 10 26
T-SQL: Number of Records is Greater Than One 7 50
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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