sp_send_dbmail; one record at a time

Question

sp_send_dbmail; one record at a time

wheelsthatgrip

Old Hand

Points: 376
More actions
December 5, 2013 at 8:32 am

#287103

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 🙂
Keith

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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 🙂
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 Old Hand Points: 376 More actions · Answer 2

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 SSC Guru Points: 323518 More actions · Answer 3

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 SSC Guru Points: 323518 More actions · Answer 4

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 Old Hand Points: 376 More actions · Answer 5

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 Old Hand Points: 376 More actions · Answer 6

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 SSC Guru Points: 323518 More actions · Answer 7

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 Old Hand Points: 376 More actions · Answer 8

Thanks Lowell! I'll give it a go and let you know what happens 🙂

Keith