asked on

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

dboyd02

ASKER

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

SrinathKomirishetty

"@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)

dboyd02

ASKER

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)

dboyd02

ASKER

Is there a way to put the @ACEEmail select into the html formated @body???

SrinathKomirishetty

@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

dboyd02

ASKER

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

ASKER CERTIFIED SOLUTION

dboyd02

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial