Can't store other language data in nvarchar column

Question

I am using SQL Server 2008. This is my Table definition.

create table myTable (nid int identity(1,1),content nvarchar(max));

when I execute these two queries

insert into myTable(content)values(N'हिंदी में परीक्षण');
update myTable set content=N'हिंदी में परीक्षण' where nid=1;

the other language data is inserted/updated in the table. I want to do the same in a stored procedure. This is the definition of my stored procedure.

create proc myProc
@nid int,
@content nvarchar(max)
as
begin
  update myTable set content=@content where nid=@nid;
end

it just doesn't work the column is updated with ????????????? value. What should I do. I tried to do this

update myTable set content=N''+@content where nid=@nid;

But this doesn't work as well. Please Help.

I forgot to tell one thing

exec myProc 1,N'हिंदी में परीक्षण'

this does work of course it does the problem is i send data from an asp .net web application where i use this syntax.

public void myProcCall()
    {
        dbcommand = db.GetStoredProcCommand("myProc", nid, content);
        ds = db.ExecuteDataSet(dbcommand);
    }

So when I send other language content from the web application the value is updated as ??????????. Do I have to make some changes in my asp .net code.

You *can* store any language. You can store any UTF16 character. MySQL doesn't have `nvarchar` though. If you can't get it to work in SQL Server, it's because you *don't* send a Unicode string or the field is not Unicode. Whatever loads the text mangled the string — Panagiotis Kanavos, Feb 06 '18 at 13:10
BTW `nvarchar(max)` is meant for blobs that can grow to 2GB, not normal text — Panagiotis Kanavos, Feb 06 '18 at 13:12
Post the code that calls the stored procedure. That's what converts Unicode to ASCII and mangles the data — Panagiotis Kanavos, Feb 06 '18 at 13:13
I tried this `exec myProc 1,N'हिंदी में'`. It works perfectly. — Prabhat G, Feb 06 '18 at 13:15
`update myTable set content=N''+@content where nid=@nid;` is pointless. First, it prepends an empty string. Second `N'whatever'` is used for *string literals*. An `nvarchar` parameter is already known to be nvarchar, it doesn't need any prefixes. Whatever calls the stored procedure mangled the text before it reach the server — Panagiotis Kanavos, Feb 06 '18 at 13:15

score 1 · Answer 1 · answered Feb 06 '18 at 13:16

This will work

EXEC myProc 1, N'हिं में पदी रीक्षण'

This won't

EXEC myProc 1, 'हिं में पदी रीक्षण'

Ergo, the parameter is sent as non-unicode

You need to ensure that the code that populate the parameter sends unicode

Can't store other language data in nvarchar column

1 Answers1