Need to set a variable or variables to this select statement

I have this select statement that I have to put into an HTML @query but I get this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

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'

Open in new window

dboyd02Asked:
Who is Participating?
 
dboyd02Connect With a Mentor Author Commented:
I figured it out. Thanks for your assistance.
DECLARE @tableHTML  NVARCHAR(MAX) ;

BEGIN


                   
  SET @tableHTML =
				N'<table border = 0> ' +
				N'<tr><th>UserName</th><th>User 3/4</th>' +
				N'<th>Token#</th></tr>' +
				Cast ((Select td = wo.NameReg,     ',           ',
							  td = p.usedBy34,     '   ',
							  td = p.serialNBR,    '   '
						From dbo.Users_Master  as wo 
                        Join dbo.secureToken as p
                        ON p.usedBy34 = wo.user34
                        where p.[Enabled] = 'No'
                        FOR XML PATH('tr'), TYPE 
						)AS NVARCHAR(MAX))  +
						
				N'</Table><br><br>' +
				N'<table border = 0> ' +
				N'<tr><th>Thanks,</th></td><br><br>' +
				N'<tr><th>Karla</th></td></table>'
                    
EXEC msdb.dbo.sp_sEND_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body_format ='HTML',
@body = @tableHTML,
@subject ='Replacement Tokens',
@FROM_address = 'Karla.workman@scresearch.net',
@execute_query_database= AsSETManagement

END

Open in new window

0
 
dboyd02Author Commented:
This is the code I tried:
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')

Open in new window

0
 
HwkrangerCommented:

Your code
[code]
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')
[/code]

The issues:

1)  You can't set a variable to a results set.  Unless it's a table, and then you have to insert it.
2)  I don't understand this statement, "I have this select statement that I have to put into an HTML @query"


0
 
dboyd02Author Commented:
Sorry, here is the bigger picture..

I need to email the results in an attachement and in the body.
DECLARE @ACEEmail varchar (max)
DECLARE @ACEEmailBody varchar (max)

BEGIN
set @ACEEmail = (select dbo.Users_Master.namereg As UserName, dbo.secureToken.usedBy34 as UserID, dbo.secureToken.serialNBR as Token# FROM dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = 'No') 

 SET @ACEEmailBody = N'<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + @ACEEmail + 
                   N'</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
  
                     
EXEC msdb.dbo.sp_sEND_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body_format ='HTML',
@body = @ACEEmailBody,
@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 the records I want emailed as an attachement but but the body comes through as unprocessed code below:


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

(select dbo.Users_Master.namereg As UserName, dbo.secureToken.usedBy34 as UserID, dbo.secureToken.serialNBR as Token# 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 As UserName, dbo.secureToken.usedBy34 as UserID, dbo.secureToken.serialNBR as Token# FROM dbo.Users_Master Left Join dbo.secureToken on dbo.secureToken.usedBy34 = dbo.Users_Master.user34 Where dbo.secureToken.[Enabled] = ''No'')' 

 SET @ACEEmailBody = N'<html><table border = 0> The following users have tokens that are expiring: Please activate the token listed below.<br> <br>' + @ACEEmail + 
                   N'</strong><br><br> Thanks, <br><br> Karla</table></body></html>'
  
                     
EXEC msdb.dbo.sp_sEND_dbmail
@recipients = 'darryl.emily@scresearch.net',
@body_format ='HTML',
@body = @ACEEmailBody,
@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
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.