| Author |
 Topic  |
|
|
mpowell80
Starting Member
USA
2 Posts |
 Posted - 11/29/2012 : 14:17:47
|
When I run the query on it's own, the appropriate record list is returned, 7 in all. However, when the mail sends through only one of the notification records from the list is displayed. What am I missing?
ALTER PROCEDURE [dbo].[spGNGNotifications]
@OpportunityID varchar(32),
@Name varchar(255)
AS
DECLARE
@NotificationMessage varchar(max),
@Notification varchar(max)
DECLARE GetNotification CURSOR FOR
SELECT NotificationMessage
FROM [gsp].[funGSP_GetGoNoGoNotifications]('E59E14B384194CD79C8C024CE7321D3B')
OPEN GetNotification
FETCH NEXT FROM GetNotification
INTO @Notification
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Notification
FETCH NEXT FROM GetNotification
INTO @Notification
END
SET @NotificationMessage = @Notification
CLOSE GetNotification
DEALLOCATE GetNotification
--EXECUTE as USER = 'me'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients ='me@me.com',
@subject = 'New Opportunity',
@body = @NotificationMessage,
@body_format = 'html'
Melissa |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
mpowell80
Starting Member
USA
2 Posts |
Posted - 11/29/2012 : 15:35:00
|
Thank you, Tara.
I moved the email portion above the ending and then received all the results, but they were in separate emails and I would like them presented all in one email together. I tried SET @NotificationMessage = @NotificationMessage + @Notification, and then the emails came through blank. Any ideas? I Appreciate your reply!
SET @NotificationMessage = @Notification
--EXECUTE as USER = 'me'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients ='me@me.com',
@subject = 'New Opportunity',
@body = @NotificationMessage,
@body_format = 'html'
END
CLOSE GetNotification
DEALLOCATE GetNotification
Melissa |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
sp_send_dbmail sending incomplete email?
