Unicode character showing question mark after using nvarchar in SQL Server

Question

In my database table I am inserting unicode character, it's working fine.

I am saving data from using .NET API using a stored procedure, but in few system unicode character are showing up as ???.

On a few other systems, it's working fine. My table columns are of type nvarchar.

Here is my query

ALTER PROCEDURE [dbo].[Sp_Account_Master_Save]
    @AccountId INT = 0,
    @AccountName NVARCHAR(250) = '',
    @GroupID INT = 0,
    @DC VARCHAR(50) = '',
    @Tag VARCHAR(50) = '',
    @OpBal DECIMAL(18, 2) = 0,
    @Address VARCHAR(50) = '',
    @OtherName NVARCHAR(50) = '',
    @LFNo VARCHAR(50) = '',
    @Area VARCHAR(50) = '',
    @City VARCHAR(50) = '',
    @State VARCHAR(50) = '',
    @Phone VARCHAR(50) = '',
    @Contact VARCHAR(50) = '',
    @Mobile1 VARCHAR(50) = '',
    @Mobile2 VARCHAR(50) = '',
    @MailID VARCHAR(50) = '',
    @BankName VARCHAR(50) = '',
    @AccNo VARCHAR(50) = '',
    @IFSC VARCHAR(50) = '',
    @GName VARCHAR(50) = '',
    @Gmobile1 VARCHAR(50) = '',
    @Gmobile2 VARCHAR(50) = '',
    @Gaddress VARCHAR(50) = '',
    @GCity VARCHAR(50) = '',
    @Gstate VARCHAR(50) = '',
    @Limit VARCHAR(50) = '',
    @AccountPhoto VARCHAR(50) = '',
    @Gphoto VARCHAR(50) = '',
    @OrganizationId INT = 0
AS
BEGIN
    INSERT INTO [dbo].[Accounts]
           ([AccountId], [AccountName], [GroupID], [DC], [Tag], [OpBal], [OtherName],[Address],
            [LFNo], [Area], [City], [State], [Phone], [Contact], [Mobile1], [Mobile2], [MailID],
            [BankName], [AccNo], [IFSC], [GName], [Gmobile1], [Gmobile2], [Gaddress], [GCity], [Gstate],
            [Limit], [AccountPhoto], [Gphoto], [OrganizationId])
    VALUES (@AccountId, @AccountName, @GroupID, @DC, @Tag, @OpBal, @OtherName, @Address,
            @LFNo, @Area, @City, @State, @Phone, @Contact, @Mobile1, @Mobile2, @MailID,
            @BankName, @AccNo, @IFSC, @GName, @Gmobile1, @Gmobile2, @Gaddress, @GCity, @Gstate,
            @Limit, @AccountPhoto, @Gphoto, @OrganizationId)
END

How to solve this issue?

question from:https://stackoverflow.com/questions/65906490/unicode-character-showing-question-mark-after-using-nvarchar-in-sql-server

Answer 1

Without know much about what you are trying to do and based on the level of control you have expressed you have and the fact that you are moving varchar and possible nvarchar fields into nvarchar fields, the first thing I would do of course is to convert the data to nvarchar before you insert so this means as an example, if the state field in the database is of type NVARCHAR then

@State VARCHAR(50)='',

should become

@State NVARCHAR(50)=N'',

Nvarchar helps with globalization of fields because it allows the database to hold non-english characters that cannot be represented in varchar because they require 2 bytes for storage vs the 1 byte for varchar.

If say you have Japanese language data being stuffed into varchar before insertion to an nvarchar field, in effect you truncate the value before it reaches its destination and may explain your issue.