title: Transform Strings Codepage IBM-850 / Windows-1252 / UTF-8
body: <p>Ok i got a database in DbaseIV format, wich have data in Codepage IBM-850 ,that i need to import to SQL server 2008. I use an asp script to import the data, recently i found problems to store special characters in strings.</p>
  
  <p>Currently this fields are varchar ... in the response of the web page everything is fine but when i try to view the data in the database this special characters are replaced or interpreted in other way.</p>
  
  <p>I'm using parametrized values in a SQL query to store data i need to convert my data from Codepage 850(IBM-850) to 1252(Windows-1252) or 65001(UTF-8).</p>
  
  <h2>Data example</h2>
  
  <pre><code>104 31  Yaxcabß NULL
  </code></pre>
  
  <p>and this must be </p>
  
  <pre><code>104 31  Yaxcabá NULL
  </code></pre>
  
  <h2>Source</h2>
  
  <p>this is the asp source</p>
  
  <pre><code>function ImportarEntidadfed(oFileName,oDBFConn)
      Dim sSQL, RSImport, oSysConn, aIData, cont
      dim keys(1), bNewData, iUpdate(1), iInsert(1), itmp
      Dim oCmd
      dim afile
      dim sResult
      sSQL = "SELECT entidad,nom_ent,mun,nom_mun,loc,nom_loc,longitud,latitud FROM " &amp; oFileName &amp; " ORDER BY entidad,mun,loc;" 
      Set oRs = oDBFConn.execute(sSQL)
      Set oSysConn = Server.CreateObject("ADODB.Connection")
      oSysConn.ConnectionString=strcon
      aIData= oRs.GetRows()
      keys(0)=""
      keys(1)="" 
      iUpdate(0)=0
      iInsert(0)=0
      iUpdate(1)=0
      iInsert(1)=0
      For cont = 0 to Ubound(aIData,2)
          if keys(0) &lt;&gt; aIData(0,cont) And aIData(0,cont) &lt;&gt; "00" Then 'Entidad federativa
              sResult = aIData(1,cont)
              oSysConn.open 
              sSQL = "SELECT * FROM yyyyyy WHERE cve_entfed= ? ;"
              '-------------------------------------------------------------------
              Set oCmd = Server.CreateObject("ADODB.Command")
              oCmd.CommandText = sSQL
              set oCmd.ActiveConnection= oSysConn
              Set oPar = oCmd.CreateParameter("cve_entfed",129,1,2,aIData(0,cont)) ' Char(2)
              oCmd.Parameters.Append(oPar)
              '-------------------------------------------------------------------
              Set oRs=oCmd.Execute()
              'Set oRs=oSysConn.execute(sSQL)
              bNewData = oRs.EOF
              'oSysConn.Close
              if bNewData Then
                  sSQL = "INSERT INTO yyyyyy (descripcion,cve_entfed) VALUES ( ? , ? );"
              Else
                  sSQL = "UPDATE yyyyyy SET descripcion= ? WHERE cve_entfed= ? ;"
              End if
  
              '-------------------------------------------------------------------
              Set oCmd = Server.CreateObject("ADODB.Command")
              oCmd.CommandText = sSQL
              set oCmd.ActiveConnection= oSysConn
              Set oPar = oCmd.CreateParameter("descripcion",200,1,255,aIData(1,cont)) 'StringSane(aIData(1,cont)) varchar(max)
              oCmd.Parameters.Append(oPar)
              Set oPar = oCmd.CreateParameter("cve_entfed",129,1,2,aIData(0,cont)) ' Char(2)
              oCmd.Parameters.Append(oPar)
              '-------------------------------------------------------------------
              'oSysConn.open
              'call oSysConn.execute(sSQL,itmp)
              Set oRs=oCmd.Execute(itmp)
  
              oSysConn.Close
              if bNewData Then
                  sResult = sResult &amp; " [INS] "
              else
                  sResult = sResult &amp; " [ACT] "
              End if
              keys(0) = aIData(0,cont)
          end if
      Next
      Set oSysConn = nothing
      ImportarEntidadfed = sResult &amp; " (Municipios " &amp; Cstr(iUpdate(0)) &amp; "[ACT] " &amp; Cstr(iInsert(0)) &amp; "[INS]) (Localidades " &amp; Cstr(iUpdate(1)) &amp; "[ACT] " &amp; CStr(iInsert(1)) &amp; "[INS])"
  end function
  
  sub Import()
      Dim oDBFConn
      Dim oDir, oFs, oFiles, oFile
      Dim sResult
  
      Set oDBFConn = Server.CreateObject ("ADODB.Connection")
      Set oFS = Server.CreateObject("Scripting.FileSystemObject")
      Set oDir = oFs.GetFolder(Server.MapPath(".\import\") &amp; "\")
      'response.write(Server.MapPath(".\import\"))
      sResult = ""
      Set oFiles = oDir.Files
  
      oDBFConn.open sDBFConn
      For Each oFile in oFiles
          aFile=Split(oFile.ShortName,".",-1,1)
          if Ubound(aFile) = 1 Then
              if Ucase(aFile(1)) = "DBF" Then
                  sResult = sResult &amp; oFile.name  &amp; ":" &amp; ImportarEntidadfed(Cstr(oFile.ShortName),oDBFConn) &amp; "&lt;br/&gt;"
              end if
          end if
      Next
      oDBFConn.Close
  
      response.write(sResult)
      Set oDBFConn = nothing
      Set oFS = nothing
      Set oDir = nothing
  End sub
  
  Call Import()
  </code></pre>
  
  <h2>Possible Solution</h2>
  
  <p>Thinking for a while about this problem, i remembered that SQL server itself has an option to convert Codepage <code>COLLATE</code> and i found maybe the Collation that fit my needs <code>SQL_1Xcompat_CP850_CI_AS</code>, but i don't know how to use it in a insert /update , and if will work at least.</p>