title: How to convert varbinary data type back to ascii in MSSQL
body: <p>I am trying to get RTF data out of a image date field in MS SQL.  Not so easy.  </p>
  
  <p>The issue is that when I do a straight binary dump of the data field it is not in RTF format.<br>
  Let me explain what is going on.  When I create a RTF file with Wordpad, and write that data to a varbinary(max), and reconvert it, the result is jiberish.  </p>
  
  <p>Code to put RTF data into MS SQL:</p>
  
  <pre><code>exec master..sp_configure 'show advanced options', 1;
  GO
  RECONFIGURE;
  GO
  exec master..sp_configure 'Ole Automation Procedures', 1;
  GO
  RECONFIGURE;
  GO
  DECLARE @objStream INT
  DECLARE @imageBinary VARBINARY(MAX)
  DECLARE @filePath VARCHAR(8000)
  
  select @imageBinary=Report from Mytable WHERE EncounterID=7
  select @filePath='c:\temp\report.rtf'
  EXEC sp_OACreate 'ADODB.Stream', @objStream OUTPUT
  EXEC sp_OASetProperty @objStream, 'Type', 1
  EXEC sp_OAMethod @objStream, 'Open'
  EXEC sp_OAMethod @objStream, 'Write', NULL, @imageBinary
  EXEC sp_OAMethod @objStream, 'SaveToFile', NULL,@filePath, 2
  EXEC sp_OAMethod @objStream, 'Close'
  EXEC sp_OADestroy @objStream
  </code></pre>
  
  <p>In binary, the first part of this file goes like this 
  0x7B 5C 72 74 66 31 5C 61 6E 73 69 5C 61 6E 73 69)
  (ascii {\rtf1\ansi\ansi )
  However, the varbinary field looks like this:
  0xB0 04 01 00 0E 00 00 00 00 00 00 00 00 00 09 00</p>
  
  <p>And when i take that data out of the database (by using the reverse of the procedure above), it is not a recognizable RTF file.  So, somehow MS is converting it in a way i can't recognize.  If I can figure out how to convert it back to ascii text then I can continue with my application.</p>