title: Conversion from VARBINARY(max) to VARCHAR(max) truncated SQL- with R
body: <p>I need to convert from a <code>varbinary(max)</code> datatype to a <code>varchar(max)</code>.</p>
  
  <p>This is the statement I use:</p>
  
  <pre><code>SELECT file = convert(varchar(max), [colFile]) FROM [myTable]  where key = 1
  </code></pre>
  
  <p><strong>This returns a truncated text.</strong></p>
  
  <p>If I understood it correctly, it is because the output of the SELECT truncates on 8000 characters. </p>
  
  <p>I checked also the following links and they explain the problem but I don't get the point how to apply it for my case.</p>
  
  <p><a href="https://stackoverflow.com/questions/3776838/convert-binary-id-field-to-text">Convert Binary Id Field to Text</a></p>
  
  <p><a href="https://stackoverflow.com/questions/12097670/how-to-store-a-string-var-greater-than-varcharmax">How to store a string var greater than varchar(max)? </a></p>
  
  <p>So... how could I get the full text? </p>
  
  <p><strong>EDIT.:</strong></p>
  
  <p>I just checked as suggested in comments and obviously the text is not truncated. It was just a SSMS thing. Therefore, the problem seems to be related to the service that is running the SQL Statement. In this case a R script.</p>
  
  <p><em>Does R have a restriction for output coming from a SQL-Statement?</em></p>
  
  <pre><code>SqlCommand commandInsert = new SqlCommand();
  commandInsert.Connection = con;
  //
  SqlCommand commandSelect= new SqlCommand();
  commandSelect.Connection = con;
  
  string sqlSelect = String.Format("select myfile = convert(varchar(max), {0})  from {1} where {2} = @fileId",
      "FileData", "FileSample", "FileId");
  commandSelect.CommandText = sqlSelect;
  commandSelect.Parameters.Add(new SqlParameter("@fileId", 1));
  var result = commandSelect.ExecuteReader();
  
  if(result.HasRows)
  {
      StringBuilder builder = new StringBuilder();
      while(result.Read())
      {
          builder.Append(result.GetString(0));
      }
      string output = @"./Data/Result.csv";
      File.WriteAllText(output, builder.ToString());
  }
  
  //
  con.Close();
  </code></pre>
  
  <p><strong>R Code</strong></p>
  
  <pre><code>&gt; install.packages("RODBC")
  &gt; library("RODBC")
  &gt; odbcChannel &lt;- odbcConnect("TestDB")
  &gt; sqlFetch(odbcChannel, "FileSample")
  Error in odbcQuery(channel, query, rows_at_time) : 
  'Calloc' could not allocate memory (214748364800 of 1 bytes)
  Además: Warning messages:
  1: In odbcQuery(channel, query, rows_at_time) :
  Reached total allocation of 8075Mb: see help(memory.size)
  2: In odbcQuery(channel, query, rows_at_time) :
  Reached total allocation of 8075Mb: see help(memory.size)
  &gt; dataFrame &lt;- sqlQuery(odbcChannel, "select myfile = convert(varchar(max), FileData)  from FileSample where FileId = 1")
  &gt; write.csv(dataFrame, file = "MyData.csv")
  </code></pre>
  
  <p>Well, I read a little bit about R(I wasn't involve in that part of the project, but for question I wanted to provide all code...)</p>
  
  <p>This is a very basic sample code where the data is fetched from the SQL-Server using the RODBC library and the output file is generated by the previous-known SQL-Statement.</p>
  
  <p>And that file is truncated. </p>
  
  <blockquote>
    <p>It should have 1000 rows(144183 characters). It throws only 442 (64233 characters)</p>
  </blockquote>
  
  <p>The file:
  <a href="http://pastebin.com/HYksyM9j" rel="nofollow noreferrer">in pastebin</a></p>