Solved

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

Posted on 2006-07-07
11
726 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

729 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