I am sending a newsletter in SQL server using a cursor. All is working fine except the first email has no html.
Here is the stored procedure:.....
DECLARE
@html varchar(max)
SET @html = (SELECT html from NewsLetter where nLID=@nLID)
DECLARE crsEmailList CURSOR FOR
SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
OPEN crsEmailList
FETCH NEXT FROM crsEmailList INTO @email, @ListID
while @@FETCH_STATUS = 0
BEGIN
DECLARE @UniqueKey varchar(20),
@UnSubscribeURL varchar(200),
@ClickURL varchar(200)
SET @UnSubscribeURL='<a href=''http://.../userfiles/OHP/UnSubscribe.aspx?listID=' + convert(varchar, @ListID) + '''>Unsubscribe</a>'
SET @ClickURL='<a href=''http://.../userfiles/OHP/clicked.aspx?Key=' + convert(varchar, @UniqueKey ) + '&URL='
EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
SET @html = (SELECT html from NewsLetter where nLID=@nLID)
SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
SET @html = Replace(@html,'<a href=\''',@ClickURL)
SET @html = Replace(@html,'[UnSubscribe]', @UnSubscribeURL )
SET @html = Replace(@html,'[date]', DATENAME(month, getdate()) )
EXEC msdb.dbo.sp_send_dbmail
@profile_Name ='Local Server',
@recipients= @email ,
@subject = @Subject,
@body = @html,
@body_format='HTML'
FETCH NEXT FROM crsEmailList INTO @email, @ListID
END
I have tried moving the line SET @html = (SELECT html from NewsLetter where nLID=@nLID) to different locations but no positive results.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire