No, you do not need to ensure that stored procedure parameters are
NVARCHAR. While there are advantages to using
NVARCHAR for both parameter and column datatypes, the fact that your Korean text is stored properly in a
VARCHAR column proves that
NVARCHAR isn't necessary.
When invoking the stored procedure from a server that uses c++ oledb, the VARCHAR value is encrypted and sent as UTF-8.
Until SQL Server 2019 (which was only released to the public as a beta a few weeks ago), SQL Server didn't support UTF-8 internally at all. So you might be supplying a UTF-8 value to the OLEDB driver, but that driver will translate the UTF-8 bytes into the equivalent bytes for the destination Code Page, which should be the Code Page used by the current database's default Collation. The "current" database is either the database you connect to (if no
USE statement has been executed), or the database set by the most recent
This means that even if the database's default Collation uses Code Page 950, and the destination column also has a Collation that uses Code Page 950, you might not get the correct data inserted if the "current" database at the time of the
EXEC of a stored procedure containing the
INSERT) has a default Collation that uses a Code Page that does not support all of the characters you are inserting.
I posted several questions in comments on the question, and answering those will greatly assist me in being able to provide a more specific answer / explanation (especially updating the question to include the exact bytes returned from the value that is not being inserted correctly). However, until then I can illustrate how this all works with the following query:
DECLARE @Test TABLE
[Value] VARCHAR(20) COLLATE Chinese_Taiwan_Bopomofo_90_CI_AS,
[HexOfCP950] AS (CONVERT(VARBINARY(20), [Value])),
[ValueAsUTF16] AS (CONVERT(NVARCHAR(20), [Value])),
[HexOfUTF16] AS (CONVERT(VARBINARY(20), CONVERT(NVARCHAR(50), [Value]))),
[NumBytes] AS (DATALENGTH([Value]))
-- The following 2 INSERTs work no matter what the current DB Collation is:
INSERT INTO @Test VALUES ('UTF-16 characters', N'韓國人學台');
INSERT INTO @Test VALUES ('CodePage 950 bytes', 0xC1FAB0EAA448BEC7A578);
-- The following 3 INSERTs simulate the effect of passing in 8-bit characters,
-- which depend on the current DB's default Collation:
INSERT INTO @Test VALUES ('CodePage 950 characters', CONVERT(VARCHAR(20), N'韓國人學台'
INSERT INTO @Test VALUES ('CodePage 949 characters', CONVERT(VARCHAR(20), N'韓國人學台'
INSERT INTO @Test VALUES ('CodePage 936 characters', CONVERT(VARCHAR(20), N'韓國人學台'
INSERT INTO @Test VALUES ('CodePage 932 characters', CONVERT(VARCHAR(20), N'韓國人學台'
INSERT INTO @Test VALUES ('CodePage 1252 characters', CONVERT(VARCHAR(20), N'韓國人學台'
SELECT * FROM @Test;
That returns the following:
Source Value HexOfCP950 ValUTF16 HexOfUTF16 Bytes
UTF-16 characters 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 950 bytes 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 950 chars 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 949 chars 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 936 chars 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 932 chars 韓國人學台 0xC1FAB0EAA448BEC7A578 韓國人學台 0xD3970B57BA4E785BF053 10
CodePage 1252 chars ????? 0x3F3F3F3F3F ????? 0x3F003F003F003F003F00 5
So it seems that those Chinese characters are found in all 4 of the Double-Byte Character Sets supported by SQL Server. So this should be working for you. Since it is not working, you need to please answer the questions I posted.
If you are executing the stored procedure from a database that has a default Collation using a Code Page that does not support the Chinese characters, that could explain why they did not get stored correctly. But:
- we did see that all four Double-Byte Character Sets supported the Chinese characters, so it would have to be a non-Chinese, non-Korean, non-Japanese Collation,
- if the Korean characters are being inserted correctly, then that is being called from a different database than where the Chinese INSERT proc is being called from.
If the stored procedure to INSERT the Chinese text is in the Chinese database, then either:
- it is possible that the default Collation for the Chinese database is not actually a Chinese Collation,
- it is possible that the Collation of the column in the table in the Chinese database is not actually a Chinese Collation.
It is also possible that the input parameter for the stored procedure is
VARCHAR(6) when it needs to be
VARCHAR(10) (at least) to hold the Chinese characters.
Also, are you encoding (not encrypting) non-UTF-8 data into UTF-8 just for the purpose of sending into SQL Server? If yes, then that is unnecessary.
Finally, if you are getting an error, you need to please provide the exact error message. Thanks.