title: Changing ntext column type to varchar(n) in SQL Server
body: <p>I'm having a problem when converting all table columns with <code>ntext</code> type in my database. I wrote this query in order to alter my columns, but there is a syntax error:</p>
  
  <pre><code>ALTER TABLE mytable ALTER COLUMN mycolumn 
  VARCHAR(SELECT MAX(DATALENGTH(mycolumn)) FROM  mytable);
  </code></pre>
  
  <p>Even though <code>SELECT MAX(DATALENGTH(mycolumn)) FROM mytable</code> is returning the correct number, the query cannot be executed.</p>
  
  <p>The syntax error is:</p>
  
  <blockquote>
    <p><em>Incorrect syntax near the keyword 'select'.(which is inside the
    <code>varchar</code>)</em></p>
  </blockquote>
  
  <p>How can I solve the problem?</p>