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=’
‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
<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=’
‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Hi Frank,<br /> Nice to hear from you again for after a couple of days. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />That’s what I was thinking to drop the column first and add a new column with the same name. But you brought up the great idea to rename and populate data and drop and rename again …. <br /><br />Thanks again for the quick response.<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">
]]>