FIX: Extended Stored Procedure xp_sendmail Does Not Handle DBCS Correctly

This article was previously published under Q299602
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352626 (SHILOH_BUGS)
SYMPTOMS
When you receive an e-mail that is sent by the xp_sendmail extended stored procedure, you may find some garbage characters in the e-mail message.

This problem only occurs when the @message and/or @subject parameters contain the double-byte character set (DBCS) characters, as demonstrated in the following example:
  -- 'N' represents a single DBCS character in this example.  declare @msg varchar(4)  select @msg = 'NN'  EXECUTE master.dbo.xp_sendmail @subject='abcdefg',@message=@msg,@recipients='test'  go  EXECUTE master.dbo.xp_sendmail @subject='NN', @recipients='test'  go				
RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
WORKAROUND
To work around this problem, use the nchar/nvarchar data type instead of the char/varchar data type.For example:
  -- 'N' represents a single DBCS character in this example,  -- except for N prefix for unicode data type.  declare @msg nvarchar(2)  select @msg = 'NN'  EXECUTE master.dbo.xp_sendmail @subject='abcdefg',@message=@msg,@recipients='test'  go  EXECUTE master.dbo.xp_sendmail @subject=N'NN', @recipients='test'  go				
STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
Properties

Article ID: 299602 - Last Review: 01/16/2015 22:44:22 - Revision: 3.2

  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB299602
Feedback