Solved

sp_send_dbmail to email many using a query

Posted on 2011-02-15
9
517 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:txdolfan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 34904523
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
 

Author Comment

by:txdolfan
ID: 34907308
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
 
LVL 22

Expert Comment

by:pivar
ID: 34909689
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 22

Expert Comment

by:pivar
ID: 34909756
If you have version 2005+ please post the code you're trying to run
0
 

Author Comment

by:txdolfan
ID: 34910116
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
 
LVL 22

Expert Comment

by:pivar
ID: 34910306
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
 

Author Comment

by:txdolfan
ID: 34911554
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
 
LVL 22

Accepted Solution

by:
pivar earned 400 total points
ID: 34911842
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
 

Author Closing Comment

by:txdolfan
ID: 34944224
Thank you for working with me through this solution.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question