title: Classic ASP with MySQL UNION
body: <p>I have a table of testimonials. Some are marked as internal, some as external. What I want to do is produce a recordset that outputs all the internal ones followed by 5 of the external ones, and these are randomly ordered. I have built the SQL successfully directly in my database, and it returns exactly what I want it to. It looks like this:</p>
  
  <pre><code>(SELECT tText, tName FROM tblTestimonials WHERE tExternal = 0 ORDER BY RAND())
  UNION
  (SELECT tText, tName FROM tblTestimonials WHERE tExternal = 1 ORDER BY RAND() LIMIT 5)
  </code></pre>
  
  <p>However when I plugged this into my Classic ASP page, which was working completely fine with a simple non-UNION SQL statement of a very similar nature, it falls over when I try and output tText. It is fine with tName (and if I add it in to the SELECT statement, it's happy with outputting tExternal too).</p>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>error '80020009'<br>
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.</p>
  </blockquote>
  
  <p><code>tName</code> (which works) is a nullable <code>varchar(500)</code></p>
  
  <p><code>tText</code> (which errors) is a nullable <code>varchar(8000)</code></p>
  
  <p>It looks as if the UNION is doing something to the data type of the larger text field. The only way I have been able to make it work is by using:</p>
  
  <pre><code>(SELECT CONVERT(tText, CHAR(500)) AS tText, tName FROM tblTestimonials WHERE tExternal = 0 ORDER BY RAND())
  UNION
  (SELECT CONVERT(tText, CHAR(500)) AS tText, tName FROM tblTestimonials WHERE tExternal = 1 ORDER BY RAND() LIMIT 5)
  </code></pre>
  
  <p>But then I end up losing a lot of data. I can only output incomplete reviews.</p>
  
  <p>If anyone has any experience with this, I'd really appreciate it.  </p>