title: Recordset field values return null from SQL server with varchar(MAX) data type
body: <p>I have an SQL 2008 server, in the server is a table that has a column with a data type of varchar(MAX). I am trying to import certain columns from this table into Excel 2010 using ADO. Here is an example of the code I am using:</p>
  
  <pre><code>Dim con As Object
  Set con = CreateObject("ADODB.Connection")
  
  cmd.CommandText = "SELECT dbo.(Table Name).Name From dbo.(Table)"
  cmd.CommandType = 1
  con.open
  cmd.ActiveConnection = con
  Dim rst as Object
  set rst = cmd.Execute
  
  ActiveSheet.cells(targetRange.Row, targetRange.Column).CopyFromRecordset rst
  </code></pre>
  
  <p>The code executes just fine, however because the column Name is set to varchar(MAX) none of the names show up in the worksheet. I know this because I made a copy of the SQL server and changed the data type for the column to varchar(1024) and it works the way it should.  </p>
  
  <p>I also tried placing the data in the recordset into the spreadsheet manually but with no luck. So the problem is I can not change the real SQL server.  </p>
  
  <p>Is there a command that I can add to the Select statement or a cast that I can use that will convert the varchar(MAX) to a data type that Excel can use, or is there something else I can do so that the data is not null when I read it in?  </p>
  
  <p>I have been looking into this problem for a day now with some threads coming close but they are talking about using select on the SQL server with a cast which doesn't work in this case, or in some other program, which doesn't help.</p>