mardi 4 août 2015

First record in SQL Cursor missing a variable

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