April 25, 2011 at 8:52 am
Hello,
I have a simple email being sent out through database mail, however, my values that I am trying to concatenate att he end of a URL does not appear to be working. As a matter of fact, the email is sent over blank. If I take the concatenated values out, it works.
Here is the code:
ALTER PROC [dbo].[usp_send_delivery_survey]
AS
DECLARE
@email_addr nvarchar(MAX)
,@cust_cd nvarchar(MAX)
,@pu_del_dt nvarchar(MAX)
,@body nvarchar(MAX);
DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT email_addr FROM dbo.DELIVERY_SURVEY;
OPEN Contacts;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM Contacts INTO @email_addr;
IF @@FETCH_STATUS = -1 BREAK;
SET @body = '<html><body><font face=verdana size=2>
<p>Thank you very much for your recent purchase from *****! We value and appreciate your business. I would like to
learn about your recent delivery by asking you for less than one minute of your time to answer five easy questions. With your
input, we hope to improve the overall delivery experience from *****.</p>
<p>Please <a href=https://www.*****.net/s/R9MPVJL?c=' + @cust_cd + '~' + @pu_del_dt + '>click here</a> to take the survey.</p>
<p>Thank you again for your time!</p>
<p>Sincerely,</p>
<p>*****<br/>
Vice President of Operations<br/>
*****</p>
</body></html>'
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Thank you for your recent purchase',
@recipients = @email_addr,
--@blind_copy_recipients = ***@*****.com; ***@*****.com',
@body_format = 'HTML',
@body = @body,
@profile_name ='SQL Server Agent';
END
CLOSE Contacts;
DEALLOCATE Contacts;
RETURN;
April 25, 2011 at 9:35 am
I do not see where a value is written to your two variables @cust_cd or @pu_del_dt if the value is NULL then the entire sting becomes NULL.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 25, 2011 at 9:55 am
Dan.Humphries (4/25/2011)
I do not see where a value is written to your two variables @cust_cd or @pu_del_dt if the value is NULL then the entire sting becomes NULL.
You have GOT to be kidding me!!!! :w00t: I cannot believe I did that!!! :crazy:
Thank you...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply