[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sp_send_dbmail to email many using a query

Posted on 2011-02-15
9
Medium Priority
?
525 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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