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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.