Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

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

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
mrmecho
Asked:
mrmecho
  • 6
  • 3
  • 2
1 Solution
 
jrb1Commented:
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
 
Anthony PerkinsCommented:
>> 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
 
mrmechoAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mrmechoAuthor Commented:
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
 
mrmechoAuthor Commented:
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
 
jrb1Commented:
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
 
mrmechoAuthor Commented:
The column is being changed from text to ntext.
0
 
mrmechoAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
jrb1Commented:
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
 
mrmechoAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now