title: SQL Server Output query to UTF8 instead of UTF16
body: <p>I have been trying to provide data to a JSON web service with the some data from a SQL query. The problem I have is that the data in stored as UTF16 in the SQL Server database and I have to provide it to the web service as UTF8.</p>
  
  <p>I want to use my SQL query to transform the data when the SQL query runs. I have been reading various forums and get the impression that there isn't a particularly easy way to do the conversion.</p>
  
  <p>For example I have names such as ÖSTLUND, which is valid Unicode/Windows code page 1253 as far as I understand.</p>
  
  <p>Am I correct in thinking that UFT8 is more like Windows code page 1252?</p>
  
  <p>I have tried doing various versions of Cast and Collate but am struggling to find which way would give me the best answer.</p>
  
  <pre><code>    (cast(SURNAME as varchar(100)) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS) 
  </code></pre>
  
  <p>or</p>
  
  <pre><code>    (cast(SURNANE as varchar(100)) COLLATE Latin1_General_100_CI_AS_KS_WS_SC)
  </code></pre>
  
  <p>Has anyone got any suggestions how best to do this? Is Cast and Collate the way to do this?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The application is an old one that is written in Uniface. The application server is running the SQL script in response to database field change. It then populates the payload of the web service post with the SQL output. The output of the SQL is made to conform to JSON for the web service.</p>