February 6, 2012 at 8:51 am
Hi,
I have a SQL Task that contains for example
Pupil School Year
1167 405 2010/2011
1646 111 2010/2011
2167 131 2010/2011
4444 242 2010/2011
Its a full results set. Set as an object.
I then want to send an email out with this table in the message source so for example I can send it out to the admin team.
I had the record set, set to a Foreach Loop container.
I had tried to create each row as a string and sending this to a send mail task. It worked but I got up to a 100 emails with one pupil each on it.
I would really like the whole lot to show as a proper table so the admin team can go through each record to run checks.
Any advice would be great.
Thanks
Debbie
February 6, 2012 at 9:32 am
I wrote an article which does a similar task but the email is sent using database mail.
The article is here.. http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/[/url]
May be it might need customizing as per your need.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 6, 2012 at 12:36 pm
You're trying to send out an email with the records in it? Why not just send an email with the records as an attachment? If you can do that, then you can output your destination into an Excel or CSV file easily enough through a Data Flow task, then attach that to the email you're sending.
February 6, 2012 at 1:06 pm
Have you tried playing with HTML body format messages? The flexibility it offers is a real eye opener:
-- make some data to play with:
SET NOCOUNT ON
DROP TABLE #Table1
CREATE TABLE #Table1 (SupplierID INT, SupplierName VARCHAR(50), Location VARCHAR(20))
INSERT INTO #Table1 (SupplierID, SupplierName, Location)
SELECT 1, 'Manzis Pie And Mash ', 'London' UNION ALL
SELECT 2, 'Zillis Deep-fried Mars Bars', 'Edinburgh'
DROP TABLE #Table2
CREATE TABLE #Table2 (TastingNotes VARCHAR(50), [Count] INT, Percentage DECIMAL(5,2))
INSERT INTO #Table2 (TastingNotes, [Count], Percentage)
SELECT 'Traditional South-East fare', 233, 75 UNION ALL
SELECT 'Feel your arteries thickening as you eat', 3, 12
-- set up the mail job:
DECLARE @tableHTML VARCHAR(MAX)
SET @tableHTML =
'<H3>First query output</H3>' +
'<table border="1">' +
'<tr>
<th>SupplierID</th>
<th>SupplierName</th>
<th>Location</th>
</tr>' +
CAST((
SELECT
td = SupplierID, '',
td = SupplierName, '',
td = Location, ''
FROM #Table1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>' +
'<H3>Second query output</H3>' +
'<table border="1">' +
'<tr>
<th>TastingNotes</th>
<th>Number of Exceptions</th>
<th>Percentage Satisfaction</th>
</tr>' +
CAST((
SELECT
td = TastingNotes, '',
td = [Count], '',
td = Percentage, ''
FROM #Table2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
'</table>'
-- run the mail job:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '', -- your value
@recipients='', -- your value
@subject = 'A nicely-formatted email body with embedded data',
@body = @tableHTML,
@body_format = 'HTML'
This is easily modified to work with an email task, but like others, I'd recommend database mail.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 7, 2012 at 3:54 am
Hi thanks for the replies.
I was hoping to not have to create additional csv files, I was hoping to keep everything as simple as possible by having them open the email up to see the list of pupil codes and incorrect school details.
Im afraid changing the HTML tags explained above is too high level for me. The tip was way above my head. I didn’t understand it at all.:blink:
I guess I will have to try at creating the csv file but if I remember correctly I had some problems with this before.
Ill let you know how I get on
Thanks again
February 7, 2012 at 4:04 am
Debbie
Instead of using an Execute SQL task to get the result set into an Object variable, why not use a Data Flow task to get it into a staging table? Then you can easily use database mail to attach the result set to the message.
John
February 7, 2012 at 4:04 am
Debbie Edwards (2/7/2012)
Hi thanks for the replies.I was hoping to not have to create additional csv files, I was hoping to keep everything as simple as possible by having them open the email up to see the list of pupil codes and incorrect school details.
Im afraid changing the HTML tags explained above is too high level for me. The tip was way above my head. I didn’t understand it at all.:blink:
I guess I will have to try at creating the csv file but if I remember correctly I had some problems with this before.
Ill let you know how I get on
Thanks again
Hi Debbie
If you can take the time to post some sample data scripts, I can help you with the HTML.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 8, 2012 at 3:17 am
Hi again,
Thanks for the offer of help. I cant believe Im having so many problems with something that should be so simple.
I decided to try connecting to an excel destination but I kept getting the error
Cannot convert between unicode and none unicode data types.
i gave up on this and connected to a flat csv file. Eveything seemed to be going well until it failed at the sending out of the email (Its worked before so im lost as to how this has happened)
The Error is [Send Mail Task] Error: An error occurred with the following error
message: "The connection "" is not found. This error is thrown by
Connections collection when the specific connection element is not found.
System.Runtime.InteropServices.COMException:
The connection "" is not found. This error is thrown by Connections
collection when the specific connection element is not found.
Ive given up for the time being because I can feel the old :angry: face coming on.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply