Characters being converted on save...

I have a situation when I try to save names or text that contains special characters, they are being converted before being saved to my SQL Server 2000 database. For example, I am trying to save the name "Bellöm-Witer" which contains the umlat and another name "Çevik". For the First Case, My SQL Profiler shows that my stored proc has the name already changed. Below is what the trace shows:
EXEC wsp_updateEditor
@editorId = 24257,
@bookId = 444,
@SEQUENCE = 1,
@FIRST_NAME = 'Leen',
@LAST_NAME = 'Bellöm-Witer',
@BIO = '',
@IMAGE_SMALL = '',
@IMAGE_LARGE = '',
@ACTIVE = 0

However, I have printed the string that contains the above SQL just before my call to execute the stored procedure and everything is fine.

Any help would be greatly appreciated!

Use N'' convention to pass the data to the stored procedure. I think you may have to hadle the data as unicode charactres in your SP as well.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hi Gaurav,

Thanks for the reply, However, I'm a little new so can you maybe give a small example for what you mean by use N" convention and handle the data as unicode in my SP.

Thanks again!

EXEC wsp_updateEditor
@editorId = 24257,
@bookId = 444,
@SEQUENCE = 1,
@FIRST_NAME = 'Leen',
@LAST_NAME = N'Bellöm-Witer',
@BIO = '',
@IMAGE_SMALL = '',
@IMAGE_LARGE = '',
@ACTIVE = 0

declare @LAST_NAME as nvarchar (20) in place of varchar(20) - assuming

See if this works out...

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Hi Guarav,

I tried it but with no luck.

Thanks

Could you please post the code that the application uses to call the stored procedure?

...And make sure that the table's fields are also defined as nvarchar and not as varchar.

Nathan H.O.
Moderator
SQL-Server-Performance.com

Hi Guarav, Nathan(vbkeyny),

Sorry I didn't post sooner, bu I just got in. Nathan, to answer you question first; the fields in my table have also been changed. Guarav, here is the code that calls the SP. Don't be alarmed by the length of it, but we use an object that loads our sql from .sql(text) files, this way we abstract all SQL statements from our code. We then substitute in any paramaters that the SQL needs. After all is complete with this object, you can see your formatted SQL statement through the property sqlMetaData.sqlSource.

With sqlMetaData
Call .loadFile("updateEditor.sql")

tokenList = .paramList
If IsArray(tokenList) Then
For itemIndex = LBound(tokenList) To UBound(tokenList) Step 1
tokenString = tokenList(itemIndex)
tokenValue = Trim(Request(tokenString) & "")
.param(tokenString) = tokenValue
Next '* itemIndex
End If '* IsArray(tokenList)

'* conn.Execute(.sqlSource)
Set getRecordSet = Server.CreateObject("ADODB.Recordset")
Call getRecordSet.Open(.sqlSource, conn, adOpenForwardOnly, adLockReadOnly)
If isSafe(getRecordSet) Then
editorId = getRecordSet.Fields.Item(0).Value
End If '* isSafe(getRecordSet)
End If '* IsArray(tokenList)
End With '* sqlMetaData

Thanks again Men!

Sorry I messed up the names on my reply!

Can you post what the SQL looks like if you print out the sqlMetaData.sqlSource instead of executing it.

/Argyle

Here is the SQL before executing it:

EXEC wsp_updateEditor
@editorId = 24257,
@bookId = 444,
@SEQUENCE = 1,
@FIRST_NAME = 'Leen',
@LAST_NAME = N'Bellöm-Witer',
@BIO = '',
@IMAGE_SMALL = '',
@IMAGE_LARGE = '',
@ACTIVE = 0

Thanks

Did you change the field definition to nvarchar?

Nathan H.O.
Moderator
SQL-Server-Performance.com

Yep, I made sure that the stored proc and tables had the change to nvarchar.

Thanks

This is really strange why it's happening. I've gone as far as checking the collation on the DB and tables.

If you are using OLEDB to connect to the database, could you try adding Auto Translate=False to the connection string... This should avoid any character conversion to ANSI

Cheers
Twan

Twan,

I am using the OLEDB Provider for ODBC Drivers to connect to the database. This is the connection provider used by the people that I am working with, and is what they want to use (?????). So, as far as I can see I do not have this option to set Auto Translate.

You have option as Twan suggested to modify the connection string by adding the attribute "AutoTranslate=no". If you don't have the modify rights to the code then suggest this to the people you are working with.



Nathan H.O.
Moderator
SQL-Server-Performance.com

Hi Guys,<br /><br />Sorry for the delay in posting, but I was away. I've tried adding the "AutoTranslate" property to my connection string, but it did not work. Here is my code:<br /><br />Dim oConn<br />Dim getRecordSet<br /><br />Set oConn = Server.CreateObject("ADODB.Connection")<br />With oConn<br />.ConnectionString = "Provider=MSDASQL.1<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=pass<img src='/community/emoticons/emotion-4.gif' alt=';P' />ersist Security Info=True;User ID=user;Data Source=data_source;Initial Catalog=init_cat;AutoTranslate=yes"<br />.Open<br />End With<br /><br />Set getRecordSet = Server.CreateObject("ADODB.Recordset")<br />'* oConn.Execute(.sqlSource)<br />Call getRecordSet.Open(.sqlSource, oConn, adOpenForwardOnly, adLockReadOnly)<br /><br />Call releaseObject(oConn)<br /><br />Thanks again!

I thought that the option was 'Auto Translate' (with the space) and with values of True or False... I dont have the guff on my laptop to try it out...

in any case True or yes is the default so you'd want False or no

Also your doing a select here, which won't uncorrupt the data that's already there... So unless you've corrected 'Bellöm-Witer' it would still come back incorrectly

Cheers
Twan