SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_send_dbmail; one record at a time


sp_send_dbmail; one record at a time

Author
Message
wheelsthatgrip
wheelsthatgrip
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 64
Hello All,

I am using dbmail to send out the results of a query. The results are being sent to SMS users so the output has to be limited to 160 characters.

My question is ......

How can I send out only one record per email (The records are less than the 160 character limit)?

Thank you for your input Smile

Keith
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67990 Visits: 40898
wheelsthatgrip (12/5/2013)
Hello All,

I am using dbmail to send out the results of a query. The results are being sent to SMS users so the output has to be limited to 160 characters.

My question is ......

How can I send out only one record per email (The records are less than the 160 character limit)?

Thank you for your input Smile

Keith


sounds like a cursor or a loop is going to be required.
if your query results has 100 rows, is that 100 users, one per message, or 100 messages for a single user?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
wheelsthatgrip
wheelsthatgrip
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 64
Hi Lowell,

Thanks for your response!

The results of the query will be sent to an AD Group. Typically there will be less than 20 records.

Here is my current SP if it helps:

USE [EQUIPMENT]
GO
/****** Object: StoredProcedure [dbo].[SP_LANES_DOWN] Script Date: 11/19/2013 11:03:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_LANES_DOWN]
AS
EXEC msdb.dbo.sp_send_dbmail
@PROFILE_NAME = 'ENTSMTP',
@RECIPIENTS = 'RECIPIENTS@ANYCOMPANY.COM',
@QUERY ='SELECT [FACILITY NAME],[FAILURE],[CALL TRACKING NUMBER],[LANE],[DATE OPEN],[TIME OPEN]
FROM [EQUIPMENT].[DBO].[Event Log]
WHERE [Intial Up/Down] = ''DOWN'' AND [UP/DOWN] IS NULL',
@BODY = 'The following facility(ies) have lanes down',
@SUBJECT = 'Lane Down Repair Request';


Thank you!

Keith
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67990 Visits: 40898
ok, so how many rows does your query return?: and please confirm that if that returns 100 rows, you want 100 emails, one per row? is that what you are after? (yeah i know it's probably only one that is 'DOWN' at any one time, but it's still relevant)


SELECT
[FACILITY NAME],
[FAILURE],
[CALL TRACKING NUMBER],
[LANE],
[DATE OPEN],
[TIME OPEN]
FROM [EQUIPMENT].[DBO].[Event Log]
WHERE [Intial Up/Down] = 'DOWN'
AND [UP/DOWN] IS NULL



it looks to me that the results from those six columns could easily exceed 160 characters:
[FACILITY NAME],
[FAILURE],
[CALL TRACKING NUMBER],
[LANE],
[DATE OPEN],
[TIME OPEN]

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67990 Visits: 40898
also, wouldn't it be better to send an email simply stating there is an alert/facilities are down,a nd direct them to a web page for details?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
wheelsthatgrip
wheelsthatgrip
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 64
Running the query with my test DB, it will currently return 3 records (This is how it looks as received in Outlook):

ASBURY PARK-SIF Brake Tester - Monitor 147571 Lane 1 HD 2013-11-12 15:25:00.0000000
Cape May Computer Failure 147582 Re-Exam 2013-11-14 08:53:23.0000000
Mays Landing Power Cord - Cabinet 147583 Lane 4 LD 2013-11-14 12:44:56.0000000

(3 rows affected)



This is how it looks received in SMS:

ASBURY PARK-SIF Brake Tester - Monitor 147571 Lane 1 HD 2013-11-12 15:25:00.0000000
Cape



I would like to send 1 email to the group for each record in the result set.

Thanks for your taking the time to look at this with me!

Keith
wheelsthatgrip
wheelsthatgrip
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 64
Lowell (12/5/2013)
also, wouldn't it be better to send an email simply stating there is an alert/facilities are down,a nd direct them to a web page for details?


Yes, I agree completely. In our current business situation, the repiar technicians can only receive this information by SMS. My origianl Idea was to put this out in a table format and have them navigate to it; I could just send the URL

Keith
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67990 Visits: 40898
ok, here's a cursor example adapted a bit to your requirements;
YOU will have to modify it to manage any date conversions/formats to varchar, as well as gracefully handle nulls, because i don't know your data.

this will send an email for each row in the query results; no rows = no emails.
i'd kind of expect to see a WHERE statement to prevent previously "notified" failures from being resent again, so if this was run every five minutes, the recipients get three emails every five minutes until the data changes otherwise.




DECLARE
@TextBody VARCHAR(160),
@FacilityName VARCHAR(30),
@Failure VARCHAR(30),
@CallTrackingNumber VARCHAR(30),
@Lane VARCHAR(30),
@DateOpen VARCHAR(30),
@TimeOpen VARCHAR(30)

DECLARE c1 CURSOR FOR
--##################################################################################################
SELECT
[FACILITY NAME],
[FAILURE],
[CALL TRACKING NUMBER],
[LANE],
CONVERT(VARCHAR,[DATE OPEN],120),
CONVERT(VARCHAR,[TIME OPEN],120)
FROM [EQUIPMENT].[DBO].[Event LOG]
WHERE [Intial Up/Down] = 'DOWN'
AND [UP/DOWN] IS NULL
--##################################################################################################
OPEN c1
FETCH NEXT FROM c1 INTO @FacilityName,@Failure,@CallTrackingNumber,@Lane,@DateOpen,@TimeOpen
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @TextBody = @FacilityName + ' '
+ @Failure + ' '
+ @CallTrackingNumber + ' '
+ @Lane + ' '
+ @DateOpen + ' '
+ @TimeOpen
EXEC msdb.dbo.sp_send_dbmail
@PROFILE_NAME = 'ENTSMTP',
@RECIPIENTS = 'RECIPIENTS@ANYCOMPANY.COM',
@BODY = @TextBody,
@SUBJECT = 'Lane Down Repair Request';
FETCH NEXT FROM c1 INTO @FacilityName,@Failure,@CallTrackingNumber,@Lane,@DateOpen,@TimeOpen
END
CLOSE c1
DEALLOCATE c1


GO



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
wheelsthatgrip
wheelsthatgrip
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 64
Thanks Lowell! I'll give it a go and let you know what happens Smile

Keith
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search