Encoding string from MS SQL

Hi all,

I have an application which will send out email in plain text in multi langauage.
the email content will be pull from txt file save in UTF-8.
i can send out email from the template with the encoding.
but when i insert data from the SQl server. the data from the SQL server are not encoded.
how do i encode the data (in other lanagauge) from sql server into UTF-8 so that it can be send together with the template.

I have try changing the data into byte and encode it in UTF-8.
but it won't displayed correctly. pls help. thanks

Unicode in SQL Server is either UCS-2 or UTF 16 and the later is the generally used version, .NET is UTF 16 by default so you can change your .NET encoding to UTF 16. The reason is NChar, NVarchar, NText, NChar max and NVarchar max are multi bytes by definition so you just need to convert to UTF 8 in your code. Hope this helps.|||

my data from the database look like this ??òú??. it is chinese simplified gb2313
the datatype for the fields is varchar.
so how do i encode it into UTF8 and it can be display in chinese.
pls help i'm totally confuse by the encoding.

|||

The Chinese you are using is Windows code page, there are six Chinese collation in SQL Server you have to find the right one in the thread below. And to UTF 8 encode in VS you start at the link below it is for VS2003 but I think it should work. So you do column level collation for your specific Chinese in SQL Server and do Unicode encoding in VS and it may be resolved. Hope this helps.

http://forums.asp.net/1067798/ShowPost.aspx

http://www.aspnetresources.com/blog/unicode_in_vsnet.aspx

|||

Hi Thanks for you advice,

base on your info i manage to find the extended proc to solve my prob xp_cp2u_web.
but i have another prob. in my stored procedure the output parameter i set it to a size of 20 data type nvarchar. it will return the result. and it will display correctly on screen. but when it is use to send via email. the rest of the content in the email is gone after my chinese character.

after some debugging, i found that it is due to the size of nvarchar i set. my chinese character size is 3. so if i set the nvarchar size to 3 all will work but if the size i set is bigger than the actual result return. it will affect the rest of the text in my email..

can anyone pls advise me on how to set the nvarchar size for my output sqlparameter.
is there a way where i can set the size of the Nvarchar as dynamic. Thanks

cmd5.CommandText = "GetEmailDetail";
cmd5.CommandType = CommandType.StoredProcedure;
cmd5.Parameters.Add("@.No", Service_ID);
SqlParameter parameterfullname = cmd5.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
parameterfullname.Direction = ParameterDirection.Output;

|||

Try the link below everything you need is covered including the correct stored procedure because that is important. When you are getting value back from a SQL Server stored procedure it is OUTPUT parameter except INT which is return value, so if in doubt always use OUTPUT if it is not needed SQL Server will ignore it. So use correct column level collation and ADO.NET OUTPUT parameters. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/