Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail; one record at a time Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, 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 :)

Keith
Post #1520132
Posted Thursday, December 5, 2013 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520151
Posted Thursday, December 5, 2013 9:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, 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
Post #1520167
Posted Thursday, December 5, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520194
Posted Thursday, December 5, 2013 9:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520197
Posted Thursday, December 5, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, 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
Post #1520208
Posted Thursday, December 5, 2013 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, 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
Post #1520212
Posted Thursday, December 5, 2013 11:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520261
Posted Thursday, December 5, 2013 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:09 AM
Points: 12, Visits: 64
Thanks Lowell! I'll give it a go and let you know what happens :)

Keith
Post #1520265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse