Pass unicode to stored procedures

Hi,
I have an **urgent** need to pass chinese characters into an SQL Server
Express table using a stored procedure. The MSDN documentation informs me
that everything will work 'automatically' if I use NVARCHAR parameters but
all I am getting is question marks. I've found old threads where other
people have the same problem but the only answer available is 'it should
just work'. Hopefully someone here (PLEASE) has more idea why the following
would not work (the result for me is that every unicode character is
converted into a question mark instead of having the correct chinese
charater).
PS The same code works fine for Jet (Access).
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
SET @.iID = 1
SET @.sName = '''
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;
--
Result: 1 '
--
NOTE: Prefixing the variable name with an "N" does fix the problem:
SET @.sName = ''' // this inserts '
SET @.sName = N''' // this inserts '
However, this solution is not workable because eventually the parameter will
be set by an existing C# application that passes a string. BTW The
'automatic' detection of string variables should make this unecessary right?
This is an urgent problem for me. A fast response would be greatly
appreciated.
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.auIs it an omen that this newsgroup filtered unicode from my original post?
The following lines that contain question marks should actually have Chinese
characters:
SET @.sName = '''
SET @.sName = ''' // this inserts question marks
SET @.sName = N''' // this inserts '
Other instances of question marks in the original post are "as intended":
Result: 1 '
Chinese characters: '
Regards,
Hamilton Colyer-Long
Janusys
www.janusys.com.au|||I've reworked the code in the original post slightly to better illustrate my
point (and because I'm desperate for help!). If you copy code below the
dotted line into studio manager and replace the 'QQQ' with Chinese
characters then the following results are obtained:
1 ? // This is failure
2 QQQ // This is success
---
DROP TABLE MyTable;
GO
DROP PROCEDURE MyTable_Insert;
GO
CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
GO
CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
AS
INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
GO
DECLARE @.RC INT
DECLARE @.iID INT
DECLARE @.sName NVARCHAR(50)
/* THIS INSERTS QUESTION MARKS */
SET @.iID = 1
SET @.sName = 'QQQ'
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
/* THIS INSERTS UNICODE */
SET @.iID = 2
SET @.sName = N'QQQ
EXECUTE @.RC = MyTable_Insert @.iID, @.sName
GO
SELECT * FROM MyTable;|||> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
As Jesse mentioned, strings in C# are Unicode. You'll get the desired
behavior as long as you parameterize your SQL statements with the correct
datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hamilton Colyer-Long" <hamilton@.NOjanusysSPAM.com.au> wrote in message
news:uD3nDInAIHA.5868@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I have an **urgent** need to pass chinese characters into an SQL Server
> Express table using a stored procedure. The MSDN documentation informs me
> that everything will work 'automatically' if I use NVARCHAR parameters but
> all I am getting is question marks. I've found old threads where other
> people have the same problem but the only answer available is 'it should
> just work'. Hopefully someone here (PLEASE) has more idea why the
> following would not work (the result for me is that every unicode
> character is converted into a question mark instead of having the correct
> chinese charater).
> PS The same code works fine for Jet (Access).
> CREATE TABLE MyTable (iID INT, sName NVARCHAR(50));
> GO
> CREATE PROCEDURE MyTable_Insert(@.iID INT, @.sName NVARCHAR(50))
> AS INSERT INTO MyTable(iID, sName) VALUES (@.iID, @.sName);
> GO
> DECLARE @.RC INT
> DECLARE @.iID INT
> DECLARE @.sName NVARCHAR(50)
> SET @.iID = 1
> SET @.sName = '''
> EXECUTE @.RC = MyTable_Insert @.iID, @.sName
> GO
> SELECT * FROM MyTable;
> --
> Result: 1 '
> --
> NOTE: Prefixing the variable name with an "N" does fix the problem:
> SET @.sName = ''' // this inserts '
> SET @.sName = N''' // this inserts '
> However, this solution is not workable because eventually the parameter
> will be set by an existing C# application that passes a string. BTW The
> 'automatic' detection of string variables should make this unecessary
> right?
> This is an urgent problem for me. A fast response would be greatly
> appreciated.
> Regards,
> Hamilton Colyer-Long
> Janusys
> www.janusys.com.au
>