locked
Invalid characters after migration RRS feed

  • Question

  • We have migrated the database from SQL Server 2012 to 2016. Post migration a specific query returns invalid characters (Chinese like characters) however the same query works fine in SQL 2012. Also tried to set the collation but still no success. Please help.

    Cheers,
    My Blog


    • Edited by Prasath C Sunday, June 9, 2019 11:34 AM
    Sunday, June 9, 2019 11:33 AM

Answers

  • I've backed up and restored the database. 

    I wouldn't expect characters to be changed with this method. Is the instance collation the same on the old and new server? Was the problem data inserted/updated post migration?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Prasath C Sunday, June 9, 2019 1:41 PM
    Sunday, June 9, 2019 11:54 AM

All replies

  • How exactly did you migrate the database? Backup/restore, detach/attach, ETL process?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, June 9, 2019 11:41 AM
  • Thanks for your reply.

    I've backed up and restored the database.

    Also the column type that returns the invalid character is sql_variant

    Following is the query and the column is MemberValue

    SELECT   Convert(nvarchar(max),MemberValue)
    from  SP2013SYDPROD_ProjectWebApp.dbo.MSP_EpmLookupTable 


    Cheers,
    My Blog


    • Edited by Prasath C Sunday, June 9, 2019 11:51 AM additional information
    Sunday, June 9, 2019 11:46 AM
  • I've backed up and restored the database. 

    I wouldn't expect characters to be changed with this method. Is the instance collation the same on the old and new server? Was the problem data inserted/updated post migration?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Prasath C Sunday, June 9, 2019 1:41 PM
    Sunday, June 9, 2019 11:54 AM
  • The new server collation is SQL_Latin1_General_CP1_CI_AS and the old server is Latin1_General_CI_AS. I see there is no issue while insert as the data source is same in both environment.


    Cheers,
    My Blog


    • Edited by Prasath C Sunday, June 9, 2019 12:03 PM
    Sunday, June 9, 2019 12:02 PM
  • I just saw your other post that the column is sql_variant and cast to nvarchar(MAX). Can you provide an example of the problem value from the SQL 2012 server?

    I wouldn't think collation would be an issue but I could be missing something. Does this query return the same collation on both servers for a problem row?

    SELECT MemberValue, CAST(MemberValue AS nvarchar(MAX)) AS MemberValueNVarchar, sql_variant_property(MemberValue, 'Collation') AS Collation
    FROM SP2013SYDPROD_ProjectWebApp.dbo.MSP_EpmLookupTable;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, June 9, 2019 12:44 PM
  • Following is the expected value

    5960 - IT and office support services

    but following is what displayed is new db

    련䇅㧝࣑֍銑펬'䳤


    Cheers,
    My Blog

    Sunday, June 9, 2019 12:54 PM
  • I see there is a difference in the source table value. Will check and get back.

    Cheers,
    My Blog

    Sunday, June 9, 2019 1:00 PM