SQL Server Performance

Characters being converted on save...

Discussion in 'General Developer Questions' started by MDean4647, Aug 21, 2003.

  1. MDean4647 New Member

    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!
  2. gaurav_bindlish New Member

    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
  3. MDean4647 New Member

    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!
  4. gaurav_bindlish New Member

    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
  5. MDean4647 New Member

    Hi Guarav,

    I tried it but with no luck.

    Thanks
  6. Twan New Member

    Could you please post the code that the application uses to call the stored procedure?
  7. vbkenya New Member

    ...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
  8. MDean4647 New Member

    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!
  9. MDean4647 New Member

    Sorry I messed up the names on my reply!
  10. Argyle New Member

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

    /Argyle
  11. MDean4647 New Member

    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
  12. vbkenya New Member

    Did you change the field definition to nvarchar?

    Nathan H.O.
    Moderator
    SQL-Server-Performance.com
  13. MDean4647 New Member

    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.
  14. Twan New Member

    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
  15. MDean4647 New Member

    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.
  16. vbkenya New Member

    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
  17. MDean4647 New Member

    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!
  18. Twan New Member

    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

Share This Page