text to varchar? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

text to varchar?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-04-11 : 14:38:58

I am trying to do this:

ALTER TABLE tblFeePaid
ALTER COLUMN NotesText varchar(8000) NULL

And it gives me this error:

Cannot alter column 'NotesText' because it is 'text'.
Warning: The table 'tblFeePaid' has been created but its maximum row size (14148) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Is there anyway I am going to be able to change the data type? I don't care if I lose some data. Thanks!

Brenda

If it weren't for you guys, where would I be?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 14:53:59

here are 2 options:
1) drop and re-add column
2) re-name table and create new table with original name.

1)
Select pkVals, substring(notesText, 1, 8000) [NotesText INTO NewTable from origTable
alter table origTable Drop Column NotesText
GO
Alter table origTable Add NotesText varchar(8000)
GO
update a set NotesText = b.NotesText from origTable a JOIN newTable b ON a.pkVals = b.pkVals

2)
sp_rename 'origTable', 'origTableBAK'
GO
Select <colList>, substring(NotesText,1,8000) as NotesText INTO origTable

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2005-04-11 : 15:14:32

What about reducing the size from 8000 to something like 5000 ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-11 : 15:34:59

Could you UPDATE the text column to be of size 8000 or less before executing the ALTER TABLE?

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-04-11 : 15:43:37

I just did 7000 instead of 8000 and it worked. Thanks for the help!

Brenda

If it weren't for you guys, where would I be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources