?
Solved

sp_send_dbmail to email many using a query

Posted on 2011-02-15
9
Medium Priority
?
520 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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