SQL Server Performance Forum – Threads Archive
Error in column alter with TEXT!
<br />Hi,<br /> How can I alter column from TEXT to varchar? It is giving me this error.<br /><br />Server: Msg 4928, Level 16, State 1, Line 4<br />Cannot alter column ‘notes’ because it is ‘text’.<br /><br />I can do it through EM/MS, but not from script.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Use Database<br />GO<br />ALTER TABLE dbo.TableName<br />ALTER COLUMN notes varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />GO<br /><br /></font id="code"></pre id="code"><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />——— <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">I think the process goes like this:<br />- add a new column notes_tmp VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />- copy over the content with UPDATE table SET notes_tmp = LEFT(notes, 12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />- drop the TEXT column<br />- rename the notes_tmp to notes<br />- rant over this very weird behaviour<br />- go home and enjoy the weekend<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />I think the process goes like this:<br />- add a new column notes_tmp VARCHAR(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />- copy over the content with UPDATE table SET notes_tmp = LEFT(notes, 12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />- drop the TEXT column<br />- rename the notes_tmp to notes<br />- rant over this very weird behaviour<br />- go home and enjoy the weekend<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>