db mail need to send select in HTML format

I am getting the folling errors on this snip of code:
Msg 8114, Level 16, State 5, Line 178
Error converting data type varchar to float.


The select code in @ACEEmail returns the results I need, but I am trying to have format contol over the email, rather than dumping the records into the email body.

Please help,
thanks

Declare @ACEEmail as float (1500)
Declare @ACEEmailBody varchar (1500)

begin
set @ACEEmail ='select dbo.Users_Master.namereg , dbo.secureToken.usedBy34, dbo.secureToken.serialNBR from dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = ''No'' '

--set @sql =  'select dbo.Users_Master.NameReg,(select top 1 dbo.secureToken.[DateDeath] from dbo.secureToken where usedBy34 = ''' + replace(@user, '''', '''''') + ''') as expiresDate from dbo.Users_Master where dbo.Users_Master.user34 = ''' + replace(@user, '''', '''''') + ''' ' 

set @ACEEmailBody = '<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + str(@ACEEmail,8,0) + '</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body = @ACEEmailBody,
@body_format ='HTML',
@subject ='Replacement Tokens',
@query = @ACEEmailBody,
@from_address = 'Karla.workman@scresearch.net',
@execute_query_database= AssetManagement
End

Open in new window

dboyd02Asked:
Who is Participating?
 
dboyd02Author Commented:
Well with this code I dont get any errors. I get the attached file with the correct records but the body just sends the SELECT statment as text see below.

The following users have tokens that are expiring: Please activate the token listed below.

select dbo.Users_Master.namereg , dbo.secureToken.usedBy34, dbo.secureToken.serialNBR FROM dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = 'No'

Thanks,

Karla

DECLARE @ACEEmail varchar (max)
DECLARE @ACEEmailBody varchar (max)

BEGIN
SET @ACEEmail ='select dbo.Users_Master.namereg , dbo.secureToken.usedBy34, dbo.secureToken.serialNBR FROM dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = ''No'' '



 SET @ACEEmailBody = '<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + @ACEEmail + '</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
EXEC msdb.dbo.sp_sEND_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body = @ACEEmailBody,
@body_format ='HTML',
@subject ='Replacement Tokens',
@query = @ACEEmail,
@FROM_address = 'Karla.workman@scresearch.net',
@execute_query_database= AsSETManagement,
@query_result_separator='    ', 
@attach_query_result_as_file = 'TRUE', 
@query_result_width=200
END

Open in new window

0
 
dboyd02Author Commented:
with this code I get:

Msg 8114, Level 16, State 5, Line 178
Error converting data type varchar to float.

-- Module to send email to ace security telling them what serialNBR to activate for what user34
--This module email ithelp and lets them know we need to order more tokens.
Declare @ACEEmail varchar (1500)
Declare @ACEEmailBody varchar (1500)

begin
set @ACEEmail ='select dbo.Users_Master.namereg , dbo.secureToken.usedBy34, dbo.secureToken.serialNBR from dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = ''No'' '



set @ACEEmailBody = '<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + str(@ACEEmail,8,0) + '</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body = @ACEEmailBody,
@body_format ='HTML',
@subject ='Replacement Tokens',
@query = @ACEEmailBody,
@from_address = 'Karla.workman@scresearch.net',
@execute_query_database= AssetManagement
End
--End Email module

Open in new window

0
 
SrinathKomirishettyCommented:
"@ACEEmail" has to be declared either nvarchar(max) or varchar(max)

Declare @ACEEmail as nvarchar(max)   -- (if you use it for exec @ACEEmail)
or
Declare @ACEEmail as varchar(max)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dboyd02Author Commented:
I get the same error:
Msg 8114, Level 16, State 5, Line 178
Error converting data type varchar to float.

if I change  DECLARE @ACEEmail varchar (max)
or
DECLARE @ACEEmail nvarchar (max)
0
 
dboyd02Author Commented:
Is there a way to put the @ACEEmail select into the html formated @body???
0
 
SrinathKomirishettyCommented:
@ACEEmail  datatype should be float as you are doing string operation  "str(@ACEEmail,8,0)".

and also assign the float value to @ACEEmail variable not the string value

0
 
dboyd02Author Commented:
I changed to this and get the same error:

Msg 8114, Level 16, State 5, Line 174
Error converting data type varchar to float.


I might be confused as to how to assign the float.
DECLARE @ACEEmail float
DECLARE @ACEEmailBody varchar (max)

BEGIN
SET @ACEEmail ='select dbo.Users_Master.namereg , dbo.secureToken.usedBy34, dbo.secureToken.serialNBR FROM dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = ''No'' '



 SET @ACEEmailBody = '<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + str(@ACEEmail,8,0) + '</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
EXEC msdb.dbo.sp_sEND_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body = @ACEEmailBody,
@body_format ='HTML',
@subject ='Replacement Tokens',
@query = @ACEEmail,
@FROM_address = 'Karla.workman@scresearch.net',
@execute_query_database= AsSETManagement,
@query_result_separator='    ', 
@attach_query_result_as_file = 'TRUE', 
@query_result_width=200
END
--END Email module

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.