SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sp_send_dbmail sending incomplete email?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mpowell80
Starting Member

USA
2 Posts

Posted - 11/29/2012 :  14:17:47  Show Profile  Reply with Quote
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
36797 Posts

Posted - 11/29/2012 :  14:49:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's because of this: SET @NotificationMessage = @Notification

Are you intending to concatenate them together? SET @NotificationMessage = @NotificationMessage + @Notification



Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mpowell80
Starting Member

USA
2 Posts

Posted - 11/29/2012 :  15:35:00  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 11/29/2012 :  16:55:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Add SET @NotificationMessage = '' before the cursor declaration so that it isn't NULL. Or you can change your ansi concat null option.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000