Problem in transferring japanese data from oracle to SQL server

[gm_dwivedi]

Hi,

I am facing problem in transferring Japanese data from oracle 9i server to Ms-Sql Server.

The Japanese characters in SQL server are not properly displayed in the Sql server table.
For each Japanese character a '?' is substituted in the table (I think that the problem is with unicode data or language support).
I am using DSN and adodc control for connecting to the databases.
The code is like:

'-----------------------------------------------------------------------

Dim con As New ADODB.Connection

Private Sub Command1_Click()
con.Execute "insert into test values('" + Adodc1.Recordset.Fields("CONTENT1_TEXT") + "')"
MsgBox "Copied"
End Sub

'----------------------------------------------------------------------
Private Sub Form_Load()
con.Open Adodc2.ConnectionString
End Sub

Private Sub Form_Unload(Cancel As Integer)
con.Close
End Sub

'------------------------------------------------------------------------

Please tell me how can i insert the correct Japanese data in the SQL server. Do I need to do anything with the SQL DB?

Regards

Gyanendra Mani Dwivedi
e-mail – gm_dwivedi@sify.com

[pclement]

It definitely sounds like a unicode problem. Assuming that everything is OK on the Oracle side what is the data type of the column where you are storing data in SQL Server? It should be NCHAR, NVARCHAR or NTEXT.

[gm_dwivedi]

Hi Paul,
Thanks for reply.
But I have to use the data type 'varchar' only in SQL server Database with SQL_Latin1_General_CP1_CI_AS Collation. Further, The data is to be used by other utility. Is there anything (API/Unicode support) in visual basic? Why the japanese data is being converted as '???...' in SQL server Database?

Regards

Gyanendra Mani Dwivedi
e-mail – gm_dwivedi@sify.com

[pclement]

AFAIK, in SQL Server you need to use the unicode supported counterpart to varchar which is nvarchar.

http://msdn.microsoft.com/library/de...es_04_6zp0.asp