USP_GENERALPURPOSEEMAIL_POLLQUEUE

Polls the General Purpose Queue for emails to process.

Parameters

Parameter	Parameter Type	Mode
@THREADID	uniqueidentifier	IN
@INSTANCEID	uniqueidentifier	IN
@ENTERPRISE	int	IN
@NETCOMMUNITY	int	IN
@EXTERNAL	int	IN
@NEWSLETTER	int	IN
@FUNDRAISER	int	IN
@CAMPAIGN	int	IN
@BLAST	int	IN
@CHAPTER	int	IN
@PERSONALBLAST	int	IN
@PROJECTAPPEALBLAST	int	IN
@PROJECTAPPEALSEED	int	IN
@SIGNUPNOTIFICATIONS	int	IN
@DONATIONNOTIFICATIONS	int	IN
@PAGESHARING	int	IN
@SITENOTIFICATIONS	int	IN
@ECARDS	int	IN
@DIRECTMARKETING	int	IN
@TESTING	int	IN

Definition

Copy



      CREATE procedure dbo.USP_GENERALPURPOSEEMAIL_POLLQUEUE
      (
        @THREADID uniqueidentifier,
        @INSTANCEID uniqueidentifier,
        @ENTERPRISE integer,
        @NETCOMMUNITY integer,
        @EXTERNAL integer,
        @NEWSLETTER integer,
        @FUNDRAISER integer,
        @CAMPAIGN integer,
        @BLAST integer,
        @CHAPTER integer,
        @PERSONALBLAST integer,
        @PROJECTAPPEALBLAST integer,
        @PROJECTAPPEALSEED integer,
        @SIGNUPNOTIFICATIONS integer,
        @DONATIONNOTIFICATIONS integer,
        @PAGESHARING integer,
        @SITENOTIFICATIONS integer,
        @ECARDS integer,
        @DIRECTMARKETING integer,
        @TESTING integer
      )
      as
      begin
         set nocount on;

         -- we need to stop a second chunk from being grabbed prior to the first chunk being submitted to shared services.  Otherwise

         -- a chunk can be sent to shared services with Guid.Empty as the external job id, and this will throw an error, and the emails

         -- in the chunk will not be sent.


        begin try
           begin tran POLLQUEUE  

                update dbo.EMAILQUEUEGENERALPURPOSE set THREADID = @THREADID, FIRSTPOLLSTARTED = 1
                where (THREADID = '00000000-0000-0000-0000-000000000000') --or CHUNKPROCESSING > 0) and 

                    and (EXTERNALJOBIDENTITY <> '00000000-0000-0000-0000-000000000000' or FIRSTPOLLSTARTED = 0) -- cannot multi-thread initial submission because chunks require a valid ext. job id.

                    and PROCESSAFTERDATE <= getutcdate() and (SOURCE in (@ENTERPRISE, @NETCOMMUNITY, @EXTERNAL))
                    and (CATEGORY in (@NEWSLETTER, @FUNDRAISER, @CAMPAIGN, @BLAST, @CHAPTER, @PERSONALBLAST, @PROJECTAPPEALBLAST, 
                                    @PROJECTAPPEALSEED, @SIGNUPNOTIFICATIONS, @DONATIONNOTIFICATIONS, @PAGESHARING, @SITENOTIFICATIONS, 
                                    @ECARDS, @DIRECTMARKETING, @TESTING)); 

                update dbo.EMAILSTATUSGENERALPURPOSE set INTERNALJOBIDENTITY = @INSTANCEID, CLIENTPROCESSSTARTDATE = isnull(CLIENTPROCESSSTARTDATE, getutcdate()) where EMAILQUEUEGENERALPURPOSEID in (select ID from dbo.EMAILQUEUEGENERALPURPOSE where THREADID = @THREADID);

                select E.ID, V.VIEWNAME, E.MERGEDATASET, E.EMAILHEADER, CONTENTTEXT, CONTENTHTML, THREADID, CHUNKPROCESSING, SOURCE, E.RETRYNUMBER, MAXRETRYNUMBER, DATEADDEDTOQUEUE, E.EXTERNALJOBIDENTITY, EMAILSTATUSGENERALPURPOSE.UNIQUEIDFIELDNAME, MERGEROWSPROCESSED,EMAILSTATUSGENERALPURPOSE.EMAILADDRESSFIELDNAME,EMAILSTATUSGENERALPURPOSE.EMAILDISPLAYNAMEFIELDNAME, EMAILSTATUSGENERALPURPOSE.MERGETOKEN, EMAILSTATUSGENERALPURPOSE.PRIORITY, E.PROCESSAFTERDATE, CATEGORY from dbo.EMAILQUEUEGENERALPURPOSE E
                inner join EMAILSTATUSGENERALPURPOSE on EMAILSTATUSGENERALPURPOSE.EMAILQUEUEGENERALPURPOSEID = E.ID
                left join dbo.SQLVIEWCATALOG V on E.MERGESQLVIEWID = V.ID
                where THREADID = @THREADID;  

            commit tran POLLQUEUE;
        end try
        begin catch
            if XACT_STATE() <> 0
                rollback tran POLLQUEUE;

            exec dbo.USP_RAISE_ERROR;
        end catch                        
      end