sp_send_dbmail problem

I'm having a problem with adding a local variable into my body of a html email sent using the sp_send_dbmail .  My code is below at the moment I'm getting this error


"The data types text and nvarchar are incompatible in the add operator."
alter procedure sp_1
 
@email nvarchar(50)
as
 
declare @username nvarchar(50)
declare @password nvarchar(50)
declare @email2 nvarchar(50)
declare @html nvarchar(max)
 
 
set @email2 = (select email from aspnet_membership where email = @email)
set @username = (select username from aspnet_Users u join aspnet_membership m on m.userid = u.userid where m.email = @email)
set @password = (select password from aspnet_membership where email = @email)
set @html = 'some html code' + @username + 'some html code';
 exec msdb.dbo.sp_send_dbmail 
		@profile_name='emailprofile', 
		@recipients= @email2 ,
		@subject= "Password",
		@body = @html,
		@body_format = html

Open in new window

leap29Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Ok, Try this one out:

Make sure that Username in the table aspnet_Users is of varchar datatype.
alter procedure sp_1
 
@email nvarchar(50)
as
declare @username nvarchar(50),
        @password nvarchar(50),
        @email2 nvarchar(50),
        @html nvarchar(max);
 
set @email2 = (select email from aspnet_membership where email = @email)
set @username = (select username from aspnet_Users u join aspnet_membership m on m.userid = u.userid where m.email = @email)
set @password = (select password from aspnet_membership where email = @email)
set @html = N'some html code' + cast(@username as Nvarchar) + N'some html code';
 exec msdb.dbo.sp_send_dbmail 
                @profile_name='emailprofile', 
                @recipients= @email2 ,
                @subject= "Password",
                @body = @html,
                @body_format = 'HTML'

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try replacing the below statement

set @html = 'some html code' + @username + 'some html code';

To

set @html = N'some html code' + cast(@username as Nvarchar) + N'some html code';

This should help you out
0
 
leap29Author Commented:
Thanks for the relpy, I've done what you suggested but I'm still getting the same error message.

The data types ntext and nvarchar are incompatible in the add operator.
 

DECLARE @HTML  NVARCHAR(MAX)
 
@html = N'some html code' + cast(@username as Nvarchar) + N'some html code';

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.