database email using dbo.sp_send_dbmail proc to send email (Network Steve Forum)

database email using dbo.sp_send_dbmail proc to send email

Hi -

I have a procedure that sends an email using database mail proc dbo.sp_send_dbmail. I have one parameter which is declared as Nvarchar(4000) and which reciecves text in any language other than english. When I execute the proc it sends an email but the any japanese text in the body is replaced with ???????.

DECLARE @EmailBody NVARCHAR(4000)
Declare @comments Nvarchar(4000)
set @comments = 't'
SELECT @EmailBody = '<html>
Client: '+ISNULL('Jennifer','')+' '+ISNULL('Matt','')+' 
Account: '+ISNULL('abc','')+' 
Score: '+ISNULL('1','')+' 
Comments: '+ISNULL(@comments,'')+' 
</html>'

-- send alert email using db_sendmail
EXEC msdb.dbo.sp_send_dbmail @recipients='abc@xyz.com', @subject='test', @body = @EmailBody, @body_format = 'HTML'

When I execute the above code then it sends an email with 'Comments' as ???????? instead some japanese text.

I tried by putting N as prefix like below but it still don't send japanese text in email body instead sends ???????

How can i solve this problem?

Thanks,

February 18th, 2015 9:31pm

Use

set @comments = N't'

Free Windows Admin Tool Kit Click here and download it now

February 18th, 2015 9:52pm

My complete code is like this:

Create Proc sendemail

(@P_Comments Nvarchar(4000))

DECLARE @EmailBody NVARCHAR(4000)
SELECT @EmailBody = '<html>
Client: '+ISNULL('Jennifer','')+' '+ISNULL('Matt','')+' 
Account: '+ISNULL('abc','')+' 
Score: '+ISNULL('1','')+' 
Comments: '+ISNULL(@P_Comments,'')+' 
</html>'

-- send alert email using db_sendmail
EXEC msdb.dbo.sp_send_dbmail @recipients='abc@xyz.com', @subject='test', @body = @EmailBody, @body_format = 'HTML'

End

and these proc gets called from vb.net code which passes the paramters @P_Comments to this calling proc 'sendemail'.

so how to do this in this scenario?

Thanks,

February 18th, 2015 10:26pm

When you assign Japanese to the parameter, you can use the way shown in above.

Free Windows Admin Tool Kit Click here and download it now

February 18th, 2015 11:56pm

Hi,

Just specify the parameter as Unicode type in your VB.Net code. something like below.

yourCommand.Parameters.Add(
"@P_Comments", SqlDbType.NVarChar, 4000).Value = "t";

February 19th, 2015 12:31am

This topic is archived. No further replies will be accepted.