Solved

sp_send_dbmail to email many using a query

Posted on 2011-02-15
9
486 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
  • 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
 
LVL 22

Expert Comment

by:pivar
ID: 34909756
If you have version 2005+ please post the code you're trying to run
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now