SQL Server 2000: Language problem

[vbUserName]

Hi friends !, maybe you can help me...

I'm developing an application in VB6 that connects to a SQL Server 2000 database.
In table NOTES I have a field called NOTE of type ntext with collation Hebrew_CI_AS, because I need to store notes in hebrew language. However, the default database language is Spanish.

The problem is: when I retrieve from the application the field NOTE, "??????" characters appear, instead of the proper hebrew characters. Are the characters correctly stored, or is it another kind of problem ? Do I have to do anything else to store hebrew characters, apart from setting the collation?

Thanks !!!!

[Krzemo]

when I retrieve from the application ...

How, what language?
Show fragment of code!

Best regards,
Krzemo.

[vbUserName]

I use an ADO recordset:

Set myRst = conn.Execute ("Select * From NOTES Where noteId = 1")

If Not myRst.Eof Then

    richtextNote.text = "" & myRst!NOTE

End If

[Krzemo]

First of all ensure that data is properly stored in db.

Change national settings in windows, in SQL Query Analizer (options for results tab) choose proper font and "Select Note From NOTES Where noteId = 1".

[vbUserName]

Ok, I've checked changing the windows and SQL Query Analyzer to hebrew, and data is not stored correctly (in english I get '???' and in hebrew 'XXX' ( where X is always the same hebrew character) ).

To store it, I use a command object that invokes a stored procedure. The parameter is taken from a RichTextBox...

Any idea? Maybe it has to do with some property of the RichTextBox?

[Krzemo]

Use string variable to store text from RichTextBox. Than use MsgBox to see if it is good or not.

Check also user connection settings. If U use ODBC than togle "Perform translation for character data".

How did U insert data to database? Stored procedure, SQL insert, or table modification (ODBC\ADO)?

Best regards,
Krzemo.

[vbUserName]

To insert the data, I use a adodb.command object, that invokes a stored procedure. I'v already tried with the string variable...


Thank you !!!

[Krzemo]

try to set "Auto Translate" connection string option.
Also take closer look to "Locale Identifier" connection string option.

I've checked changing the windows and SQL Query Analyzer to hebrew, and data is not stored correctly (in english I get '???' and in hebrew 'XXX' ( where X is always the same hebrew character) ).

Try to call that SP from SQL Query Analyzer. Is data stored properly?

Usualy "???" means that database locale differs from client locale that much(different code page) that they cannot be converted to database(column) locale character (no corresponding character in database codepage). It was common problem in SQL 7.0 and in SQL 2000 if choosen collation compatibility mode with SQL 7.0.

Hope it helps.

[vbUserName]

Thank you for your concern !!!

Executing and viewing the table from SQL Query Analyzer, I see that data is not stored correctly (neither when I see results in english nor in hebrew).

For the column I use Hebrew_CS_AI collation.

I have Sql Server 2000, I don't think I've chosen compatibility with 7.0 ...

Any other idea? Thanks !