Transform Strings Codepage IBM-850 / Windows-1252 / UTF-8

Question

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.

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.

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).

Data example

104 31  Yaxcabß NULL

and this must be

104 31  Yaxcabá NULL

Source

this is the asp source

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 " & oFileName & " 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) <> aIData(0,cont) And aIData(0,cont) <> "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 & " [INS] "
            else
                sResult = sResult & " [ACT] "
            End if
            keys(0) = aIData(0,cont)
        end if
    Next
    Set oSysConn = nothing
    ImportarEntidadfed = sResult & " (Municipios " & Cstr(iUpdate(0)) & "[ACT] " & Cstr(iInsert(0)) & "[INS]) (Localidades " & Cstr(iUpdate(1)) & "[ACT] " & CStr(iInsert(1)) & "[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\") & "\")
    '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 & oFile.name  & ":" & ImportarEntidadfed(Cstr(oFile.ShortName),oDBFConn) & "<br/>"
            end if
        end if
    Next
    oDBFConn.Close

    response.write(sResult)
    Set oDBFConn = nothing
    Set oFS = nothing
    Set oDir = nothing
End sub

Call Import()

Possible Solution

Thinking for a while about this problem, i remembered that SQL server itself has an option to convert Codepage COLLATE and i found maybe the Collation that fit my needs SQL_1Xcompat_CP850_CI_AS, but i don't know how to use it in a insert /update , and if will work at least.

Answer 1

If i understand you correct you want to change the collation on the column and the database.

If so the you have to first change the collation of the database so if you add new column then these are also in the same collation.

This sql checks the current collation for the database

SELECT DATABASEPROPERTYEX('YourDatabase', 'Collation') SQLCollation;

Then to change the database collation use this statement

ALTER DATABASE YourDatabase
COLLATE French_CI_AI ;

Then you have to change the collation of the column. Use this statement to see what collation the columns are

SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'YourTable')

Then to change the columns collation you can use this statement

ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

I hope this helps