[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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

0
leap29
Asked:
leap29
  • 2
1 Solution
 
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
 
Raja Jegan RSQL 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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now