sp_send_dbmail to email many using a query

I am trying to send out an email to users who have outstanding issues within the trouble ticketing system. I want to use sp_send_dbmail

The query below selects the criteria for the emails; however if a user has multiple issues I want to group them into one email. The users.email is the email address and the issues.issuenbr is the unique identifier for the issue.

select users.email,issues.issuenbr,issues.issuedescription from issues (nolock)
join users (nolock) on issues.submittedby = users.userid
where issuesolution is NULL and substatusID = 11

Any assistance is appreciated.
txdolfanAsked:
Who is Participating?
 
pivarConnect With a Mentor Commented:
No FOR XML PATH was introduced in 2005, but here's another solution for 2000:

CREATE FUNCTION issueslist(@id int)
RETURNS varchar(8000)
AS BEGIN
  DECLARE @result varchar(8000)
  SELECT @result=COALESCE(@result+CHAR(10),'')+CONVERT(varchar,i.issuenbr)+'.'+i.issuedescription FROM issues i WHERE i.submittedby=@id AND i.issuesolution is NULL AND i.substatusID = 11
  RETURN @result
END
GO

SELECT u.email, dbo.issueslist(u.userid) AS issueslist FROM users u WHERE EXISTS (SELECT 1 FROM issues i2 WHERE i2.submittedby=u.userid AND i2.issuesolution is NULL and i2.substatusID = 11)

Open in new window

0
 
pivarCommented:
Hi,

As the mailbody is a solid textblock you could try this. Issueslist is the list of issues per user. I assume isseuenbr is not chars.

SELECT u.email,
STUFF((SELECT CHAR(13)+CHAR(10)+CONVERT(varchar,i.issuenbr)+'.'+i.issuedescription AS[text()] FROM issues i WHERE i.submittedby=u.userid AND i.issuesolution is NULL AND i.substatusID = 11 FOR XML PATH('')), 1, 2, '') AS issueslist
FROM users u
WHERE EXISTS (SELECT 1 FROM issues i2 WHERE i2.submittedby=u.userid AND i2.issuesolution is NULL and i2.substatusID = 11)

/peter
0
 
txdolfanAuthor Commented:
Pivar - It gives me a format error. I am not familiar with the "STUFF" syntax.
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
pivarCommented:
Which version of sql server do you have? You must have at least 2005 for this solution.

Also a small change

SELECT u.email,
STUFF((SELECT CHAR(10)+CONVERT(varchar,i.issuenbr)+'.'+i.issuedescription AS[text()] FROM issues i WHERE i.submittedby=u.userid AND i.issuesolution is NULL AND i.substatusID = 11 FOR XML PATH('')), 1, 1, '') AS issueslist
FROM users u
WHERE EXISTS (SELECT 1 FROM issues i2 WHERE i2.submittedby=u.userid AND i2.issuesolution is NULL and i2.substatusID = 11)
0
 
pivarCommented:
If you have version 2005+ please post the code you're trying to run
0
 
txdolfanAuthor Commented:
I am just trying to run the code you have posted and it gives me the error. It is a DEV version of sql server.
0
 
pivarCommented:
OK, but is it sql server 2005? Or sql server 2000?  
I attach the code in a code window instead, if it's a copy/paste problem. If you're running sql  server 2005/2008 try copy this code instead.
SELECT u.email,
STUFF((SELECT CHAR(10)+CONVERT(varchar,i.issuenbr)+'.'+i.issuedescription AS[text()] FROM issues i WHERE i.submittedby=u.userid AND i.issuesolution is NULL AND i.substatusID = 11 FOR XML PATH('')), 1, 1, '') AS issueslist
FROM users u
WHERE EXISTS (SELECT 1 FROM issues i2 WHERE i2.submittedby=u.userid AND i2.issuesolution is NULL and i2.substatusID = 11)

Open in new window

0
 
txdolfanAuthor Commented:
It is 2000. I didn't realize it was that old. Can I still do this or do I need to migrate the database?
0
 
txdolfanAuthor Commented:
Thank you for working with me through this solution.
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.