microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

un-answered question got removed

Subject: un-answered question got removed
From: "InvalidLastName"
Date: Thu, 14 Jul 2005 17:59:23 -0400
Newsgroups: microsoft.public.sqlserver.notificationsvcs
ï
Hi,
I posted a qustion in this newsgroup on 07/08 without getting any responses. However, for some reasons, the post has been removed from the news server today (at least I cannot see it from my outlook express)
 
 
 
Any reason my un-answered question got removed ?
 
 
 
<><><><><><><><> original post <><><><><><><><>
 
 
Recently we started stress testing our SQLNS (for SQL2K) application. Here are some basic background information:
- running SQLNS enterprise sp1 v2.0.3008.0
- 1 generator, 2 distributors (W2k3 boxes)
- custom content formatter and custom delivery component (the delivery component uses notificationStatusCallback to report NotificationStatus to distributor, as shown as
http://msdn.microsoft.com/library/default.asp?url="">)
- quad CPU SQL server cluster w/ 4G memory
- basic distributor logging (see ADF below)

<DistributorLogging>

<LogBeforeDeliveryAttempts>false</LogBeforeDeliveryAttempts>

<LogStatusInfo>false</LogStatusInfo>

<LogNotificationText>false</LogNotificationText>

</DistributorLogging>


The most serious performance issue we encoutered is average 75% CPU usage on SQL server while distributors were distributing. At the time the SQLNS application server CPU usage was at only about 10%. This is a totally unacceptable result.

After we dug more on this problem, we found out the high CPU was caused by NSInsertESSNotificationsDistributionLogEntries stored procedure. This stored procedure has "300" parameters, and "100" IF/ELSE blocks. I believe it is used by StatusCallback to log the results back to notification table and NSDistributionLog table. It was designed to log up to 100 notifications at a time (3 parameters per record). However, in our case, we only need 3 out of 300 parameters and the first IF/ELSE for logging just ONE notification.

Here are the alternatives we experimented and their results:

A) "Hacked" NSInsertESSNotificationsDistributionLogEntries stored procedure by returning to calling program (added "return" statement right after the first IF/ELSE. The result was the SQL server CPU usage dropped from average 75% to 7%. Yeah, 10 times difference by just bypass 99 IF/ELSE blocks in the stored procedure. The SQLNS application was able to deliver 50 notifications/second/distributor

B) Skip calling notificationStatusCallback for sucessfully delivered notification. This will totally remove the logging, however, I believe Distributor also uses notificationStatusCallback to determine the the result of current batch (work item). Because no successful status reported by delivery component, the distributor marked the current work item as "failure" , and this work item will be picked up by distributor again at next retry. (We actually wrote a custom stored procedure which mimics the 300-parameter NSInsertESSNotificationsDistributionLogEntries to just update the notification status. But distributor marked the work item "failure" because no sucessful status reported back by custom delivery component. However, those "successful" delivered notification (deliverystatuscode =6) were not picked up again by distributor during retry)

C) Changed custom delivery component to "cache" the NotificationStatus for each notification,and submitted the NotificationStatus in batch (when cache size reaches 100, or in Flush() to log all the cached NotificationStatus). SQL server CPU dropped from 75% to 10%.

The alternative A resulted the best performance and very healthy CPU usage pattern, and option C is a close second. We don't consider to ues option B because I don't think it is right to skip the notificationStatusCallback in delivery component

The option A requires to "hack" the SQLNS stored procedure, which I try to avoid. However, the option C will cause some status cached notification being re-sent if distributor encouters server failure while distributing the notification. Since the status is not logged immediately, the SQLNS will treat those un-reported notifications as never delivered, and re-send those at retry.

The last thing we want to do is to modify the SQLNS stored procedures. However, it is also important for us not resending duplicate notifications.

Here are some questions:
- Has Anyone seen the similar high CPU usage from basic logging as we encoutered (I know from SQLNS BOL, the logging is not recommended, because distributor already has basic logging. But this "basic" logging is killing us. It doesn't make any sense to me)

- Did we terriblely do something wrong in our SQLNS application? We seperate the data files, log files and index files. The event and subscription are on different file groups. I don't think the hard drive is not an issue here. We are using production cluster to perform stress testing.

- There is really no way for a SQLNS application can deliver over 400 notifications/second as described in
http://msdn.microsoft.com/library/default.asp?url=""> . It doesn't matter how fast is the distributor, or how many distributors, the logging just kills the SQL server. I am not quite sure how can they achieve those numbers while running typical SQLNS configuration

- How come just 100 IF/ELSE blocks in a stored procedure can cause such high CPU usages on SQL server? I never expect that updating 100 records cross network via connection pooling is even more efficient then wrapping update in 100 IF/ELSE blocks

Any suggestion would be appreciated

Thanks

ILN





--------------------------------------------------------------------------------

Her it comes, one of the two monster logging stored procedure which has 300 parameters and 100 IF/ELSE blocks (the other one is NSInsertESSNotificationsDistributionLogEntries )

PROCEDURE dbo."NSSetESSNotificationsNotificationDeliveryStatus" (
@distributorId int,
@callerCurrentUtcTime datetime,
@notificationId0 bigint = NULL,
@workItemId0 bigint = NULL,
@deliveryStatus0 tinyint = NULL,
@callerSentTime0 datetime = NULL,
@deliveryStatusInfo0 nvarchar(2048) = NULL,
@notificationText0 nvarchar(2048) = NULL,
@notificationId1 bigint = NULL,
@workItemId1 bigint = NULL,
@deliveryStatus1 tinyint = NULL,
@callerSentTime1 datetime = NULL,
@deliveryStatusInfo1 nvarchar(2048) = NULL,
@notificationText1 nvarchar(2048) = NULL,
@notificationId2 bigint = NULL,
@workItemId2 bigint = NULL,
@deliveryStatus2 tinyint = NULL,
@callerSentTime2 datetime = NULL,
@deliveryStatusInfo2 nvarchar(2048) = NULL,
@notificationText2 nvarchar(2048) = NULL,
@notificationId3 bigint = NULL,
@workItemId3 bigint = NULL,
@deliveryStatus3 tinyint = NULL,
@callerSentTime3 datetime = NULL,
@deliveryStatusInfo3 nvarchar(2048) = NULL,
@notificationText3 nvarchar(2048) = NULL,
@notificationId4 bigint = NULL,
@workItemId4 bigint = NULL,
@deliveryStatus4 tinyint = NULL,
@callerSentTime4 datetime = NULL,
@deliveryStatusInfo4 nvarchar(2048) = NULL,
@notificationText4 nvarchar(2048) = NULL,
@notificationId5 bigint = NULL,
@workItemId5 bigint = NULL,
@deliveryStatus5 tinyint = NULL,
@callerSentTime5 datetime = NULL,
@deliveryStatusInfo5 nvarchar(2048) = NULL,
@notificationText5 nvarchar(2048) = NULL,
@notificationId6 bigint = NULL,
@workItemId6 bigint = NULL,
@deliveryStatus6 tinyint = NULL,
@callerSentTime6 datetime = NULL,
@deliveryStatusInfo6 nvarchar(2048) = NULL,
@notificationText6 nvarchar(2048) = NULL,
@notificationId7 bigint = NULL,
@workItemId7 bigint = NULL,
@deliveryStatus7 tinyint = NULL,
@callerSentTime7 datetime = NULL,
@deliveryStatusInfo7 nvarchar(2048) = NULL,
@notificationText7 nvarchar(2048) = NULL,
@notificationId8 bigint = NULL,
@workItemId8 bigint = NULL,
@deliveryStatus8 tinyint = NULL,
@callerSentTime8 datetime = NULL,
@deliveryStatusInfo8 nvarchar(2048) = NULL,
@notificationText8 nvarchar(2048) = NULL,
@notificationId9 bigint = NULL,
@workItemId9 bigint = NULL,
@deliveryStatus9 tinyint = NULL,
@callerSentTime9 datetime = NULL,
@deliveryStatusInfo9 nvarchar(2048) = NULL,
@notificationText9 nvarchar(2048) = NULL,
@notificationId10 bigint = NULL,
@workItemId10 bigint = NULL,
@deliveryStatus10 tinyint = NULL,
@callerSentTime10 datetime = NULL,
@deliveryStatusInfo10 nvarchar(2048) = NULL,
@notificationText10 nvarchar(2048) = NULL,
@notificationId11 bigint = NULL,
@workItemId11 bigint = NULL,
@deliveryStatus11 tinyint = NULL,
@callerSentTime11 datetime = NULL,
@deliveryStatusInfo11 nvarchar(2048) = NULL,
@notificationText11 nvarchar(2048) = NULL,
@notificationId12 bigint = NULL,
@workItemId12 bigint = NULL,
@deliveryStatus12 tinyint = NULL,
@callerSentTime12 datetime = NULL,
@deliveryStatusInfo12 nvarchar(2048) = NULL,
@notificationText12 nvarchar(2048) = NULL,
@notificationId13 bigint = NULL,
@workItemId13 bigint = NULL,
@deliveryStatus13 tinyint = NULL,
@callerSentTime13 datetime = NULL,
@deliveryStatusInfo13 nvarchar(2048) = NULL,
@notificationText13 nvarchar(2048) = NULL,
@notificationId14 bigint = NULL,
@workItemId14 bigint = NULL,
@deliveryStatus14 tinyint = NULL,
@callerSentTime14 datetime = NULL,
@deliveryStatusInfo14 nvarchar(2048) = NULL,
@notificationText14 nvarchar(2048) = NULL,
@notificationId15 bigint = NULL,
@workItemId15 bigint = NULL,
@deliveryStatus15 tinyint = NULL,
@callerSentTime15 datetime = NULL,
@deliveryStatusInfo15 nvarchar(2048) = NULL,
@notificationText15 nvarchar(2048) = NULL,
@notificationId16 bigint = NULL,
@workItemId16 bigint = NULL,
@deliveryStatus16 tinyint = NULL,
@callerSentTime16 datetime = NULL,
@deliveryStatusInfo16 nvarchar(2048) = NULL,
@notificationText16 nvarchar(2048) = NULL,
@notificationId17 bigint = NULL,
@workItemId17 bigint = NULL,
@deliveryStatus17 tinyint = NULL,
@callerSentTime17 datetime = NULL,
@deliveryStatusInfo17 nvarchar(2048) = NULL,
@notificationText17 nvarchar(2048) = NULL,
@notificationId18 bigint = NULL,
@workItemId18 bigint = NULL,
@deliveryStatus18 tinyint = NULL,
@callerSentTime18 datetime = NULL,
@deliveryStatusInfo18 nvarchar(2048) = NULL,
@notificationText18 nvarchar(2048) = NULL,
@notificationId19 bigint = NULL,
@workItemId19 bigint = NULL,
@deliveryStatus19 tinyint = NULL,
@callerSentTime19 datetime = NULL,
@deliveryStatusInfo19 nvarchar(2048) = NULL,
@notificationText19 nvarchar(2048) = NULL,
@notificationId20 bigint = NULL,
@workItemId20 bigint = NULL,
@deliveryStatus20 tinyint = NULL,
@callerSentTime20 datetime = NULL,
@deliveryStatusInfo20 nvarchar(2048) = NULL,
@notificationText20 nvarchar(2048) = NULL,
@notificationId21 bigint = NULL,
@workItemId21 bigint = NULL,
@deliveryStatus21 tinyint = NULL,
@callerSentTime21 datetime = NULL,
@deliveryStatusInfo21 nvarchar(2048) = NULL,
@notificationText21 nvarchar(2048) = NULL,
@notificationId22 bigint = NULL,
@workItemId22 bigint = NULL,
@deliveryStatus22 tinyint = NULL,
@callerSentTime22 datetime = NULL,
@deliveryStatusInfo22 nvarchar(2048) = NULL,
@notificationText22 nvarchar(2048) = NULL,
@notificationId23 bigint = NULL,
@workItemId23 bigint = NULL,
@deliveryStatus23 tinyint = NULL,
@callerSentTime23 datetime = NULL,
@deliveryStatusInfo23 nvarchar(2048) = NULL,
@notificationText23 nvarchar(2048) = NULL,
@notificationId24 bigint = NULL,
@workItemId24 bigint = NULL,
@deliveryStatus24 tinyint = NULL,
@callerSentTime24 datetime = NULL,
@deliveryStatusInfo24 nvarchar(2048) = NULL,
@notificationText24 nvarchar(2048) = NULL,
@notificationId25 bigint = NULL,
@workItemId25 bigint = NULL,
@deliveryStatus25 tinyint = NULL,
@callerSentTime25 datetime = NULL,
@deliveryStatusInfo25 nvarchar(2048) = NULL,
@notificationText25 nvarchar(2048) = NULL,
@notificationId26 bigint = NULL,
@workItemId26 bigint = NULL,
@deliveryStatus26 tinyint = NULL,
@callerSentTime26 datetime = NULL,
@deliveryStatusInfo26 nvarchar(2048) = NULL,
@notificationText26 nvarchar(2048) = NULL,
@notificationId27 bigint = NULL,
@workItemId27 bigint = NULL,
@deliveryStatus27 tinyint = NULL,
@callerSentTime27 datetime = NULL,
@deliveryStatusInfo27 nvarchar(2048) = NULL,
@notificationText27 nvarchar(2048) = NULL,
@notificationId28 bigint = NULL,
@workItemId28 bigint = NULL,
@deliveryStatus28 tinyint = NULL,
@callerSentTime28 datetime = NULL,
@deliveryStatusInfo28 nvarchar(2048) = NULL,
@notificationText28 nvarchar(2048) = NULL,
@notificationId29 bigint = NULL,
@workItemId29 bigint = NULL,
@deliveryStatus29 tinyint = NULL,
@callerSentTime29 datetime = NULL,
@deliveryStatusInfo29 nvarchar(2048) = NULL,
@notificationText29 nvarchar(2048) = NULL,
@notificationId30 bigint = NULL,
@workItemId30 bigint = NULL,
@deliveryStatus30 tinyint = NULL,
@callerSentTime30 datetime = NULL,
@deliveryStatusInfo30 nvarchar(2048) = NULL,
@notificationText30 nvarchar(2048) = NULL,
@notificationId31 bigint = NULL,
@workItemId31 bigint = NULL,
@deliveryStatus31 tinyint = NULL,
@callerSentTime31 datetime = NULL,
@deliveryStatusInfo31 nvarchar(2048) = NULL,
@notificationText31 nvarchar(2048) = NULL,
@notificationId32 bigint = NULL,
@workItemId32 bigint = NULL,
@deliveryStatus32 tinyint = NULL,
@callerSentTime32 datetime = NULL,
@deliveryStatusInfo32 nvarchar(2048) = NULL,
@notificationText32 nvarchar(2048) = NULL,
@notificationId33 bigint = NULL,
@workItemId33 bigint = NULL,
@deliveryStatus33 tinyint = NULL,
@callerSentTime33 datetime = NULL,
@deliveryStatusInfo33 nvarchar(2048) = NULL,
@notificationText33 nvarchar(2048) = NULL,
@notificationId34 bigint = NULL,
@workItemId34 bigint = NULL,
@deliveryStatus34 tinyint = NULL,
@callerSentTime34 datetime = NULL,
@deliveryStatusInfo34 nvarchar(2048) = NULL,
@notificationText34 nvarchar(2048) = NULL,
@notificationId35 bigint = NULL,
@workItemId35 bigint = NULL,
@deliveryStatus35 tinyint = NULL,
@callerSentTime35 datetime = NULL,
@deliveryStatusInfo35 nvarchar(2048) = NULL,
@notificationText35 nvarchar(2048) = NULL,
@notificationId36 bigint = NULL,
@workItemId36 bigint = NULL,
@deliveryStatus36 tinyint = NULL,
@callerSentTime36 datetime = NULL,
@deliveryStatusInfo36 nvarchar(2048) = NULL,
@notificationText36 nvarchar(2048) = NULL,
@notificationId37 bigint = NULL,
@workItemId37 bigint = NULL,
@deliveryStatus37 tinyint = NULL,
@callerSentTime37 datetime = NULL,
@deliveryStatusInfo37 nvarchar(2048) = NULL,
@notificationText37 nvarchar(2048) = NULL,
@notificationId38 bigint = NULL,
@workItemId38 bigint = NULL,
@deliveryStatus38 tinyint = NULL,
@callerSentTime38 datetime = NULL,
@deliveryStatusInfo38 nvarchar(2048) = NULL,
@notificationText38 nvarchar(2048) = NULL,
@notificationId39 bigint = NULL,
@workItemId39 bigint = NULL,
@deliveryStatus39 tinyint = NULL,
@callerSentTime39 datetime = NULL,
@deliveryStatusInfo39 nvarchar(2048) = NULL,
@notificationText39 nvarchar(2048) = NULL,
@notificationId40 bigint = NULL,
@workItemId40 bigint = NULL,
@deliveryStatus40 tinyint = NULL,
@callerSentTime40 datetime = NULL,
@deliveryStatusInfo40 nvarchar(2048) = NULL,
@notificationText40 nvarchar(2048) = NULL,
@notificationId41 bigint = NULL,
@workItemId41 bigint = NULL,
@deliveryStatus41 tinyint = NULL,
@callerSentTime41 datetime = NULL,
@deliveryStatusInfo41 nvarchar(2048) = NULL,
@notificationText41 nvarchar(2048) = NULL,
@notificationId42 bigint = NULL,
@workItemId42 bigint = NULL,
@deliveryStatus42 tinyint = NULL,
@callerSentTime42 datetime = NULL,
@deliveryStatusInfo42 nvarchar(2048) = NULL,
@notificationText42 nvarchar(2048) = NULL,
@notificationId43 bigint = NULL,
@workItemId43 bigint = NULL,
@deliveryStatus43 tinyint = NULL,
@callerSentTime43 datetime = NULL,
@deliveryStatusInfo43 nvarchar(2048) = NULL,
@notificationText43 nvarchar(2048) = NULL,
@notificationId44 bigint = NULL,
@workItemId44 bigint = NULL,
@deliveryStatus44 tinyint = NULL,
@callerSentTime44 datetime = NULL,
@deliveryStatusInfo44 nvarchar(2048) = NULL,
@notificationText44 nvarchar(2048) = NULL,
@notificationId45 bigint = NULL,
@workItemId45 bigint = NULL,
@deliveryStatus45 tinyint = NULL,
@callerSentTime45 datetime = NULL,
@deliveryStatusInfo45 nvarchar(2048) = NULL,
@notificationText45 nvarchar(2048) = NULL,
@notificationId46 bigint = NULL,
@workItemId46 bigint = NULL,
@deliveryStatus46 tinyint = NULL,
@callerSentTime46 datetime = NULL,
@deliveryStatusInfo46 nvarchar(2048) = NULL,
@notificationText46 nvarchar(2048) = NULL,
@notificationId47 bigint = NULL,
@workItemId47 bigint = NULL,
@deliveryStatus47 tinyint = NULL,
@callerSentTime47 datetime = NULL,
@deliveryStatusInfo47 nvarchar(2048) = NULL,
@notificationText47 nvarchar(2048) = NULL,
@notificationId48 bigint = NULL,
@workItemId48 bigint = NULL,
@deliveryStatus48 tinyint = NULL,
@callerSentTime48 datetime = NULL,
@deliveryStatusInfo48 nvarchar(2048) = NULL,
@notificationText48 nvarchar(2048) = NULL,
@notificationId49 bigint = NULL,
@workItemId49 bigint = NULL,
@deliveryStatus49 tinyint = NULL,
@callerSentTime49 datetime = NULL,
@deliveryStatusInfo49 nvarchar(2048) = NULL,
@notificationText49 nvarchar(2048) = NULL,
@notificationId50 bigint = NULL,
@workItemId50 bigint = NULL,
@deliveryStatus50 tinyint = NULL,
@callerSentTime50 datetime = NULL,
@deliveryStatusInfo50 nvarchar(2048) = NULL,
@notificationText50 nvarchar(2048) = NULL,
@notificationId51 bigint = NULL,
@workItemId51 bigint = NULL,
@deliveryStatus51 tinyint = NULL,
@callerSentTime51 datetime = NULL,
@deliveryStatusInfo51 nvarchar(2048) = NULL,
@notificationText51 nvarchar(2048) = NULL,
@notificationId52 bigint = NULL,
@workItemId52 bigint = NULL,
@deliveryStatus52 tinyint = NULL,
@callerSentTime52 datetime = NULL,
@deliveryStatusInfo52 nvarchar(2048) = NULL,
@notificationText52 nvarchar(2048) = NULL,
@notificationId53 bigint = NULL,
@workItemId53 bigint = NULL,
@deliveryStatus53 tinyint = NULL,
@callerSentTime53 datetime = NULL,
@deliveryStatusInfo53 nvarchar(2048) = NULL,
@notificationText53 nvarchar(2048) = NULL,
@notificationId54 bigint = NULL,
@workItemId54 bigint = NULL,
@deliveryStatus54 tinyint = NULL,
@callerSentTime54 datetime = NULL,
@deliveryStatusInfo54 nvarchar(2048) = NULL,
@notificationText54 nvarchar(2048) = NULL,
@notificationId55 bigint = NULL,
@workItemId55 bigint = NULL,
@deliveryStatus55 tinyint = NULL,
@callerSentTime55 datetime = NULL,
@deliveryStatusInfo55 nvarchar(2048) = NULL,
@notificationText55 nvarchar(2048) = NULL,
@notificationId56 bigint = NULL,
@workItemId56 bigint = NULL,
@deliveryStatus56 tinyint = NULL,
@callerSentTime56 datetime = NULL,
@deliveryStatusInfo56 nvarchar(2048) = NULL,
@notificationText56 nvarchar(2048) = NULL,
@notificationId57 bigint = NULL,
@workItemId57 bigint = NULL,
@deliveryStatus57 tinyint = NULL,
@callerSentTime57 datetime = NULL,
@deliveryStatusInfo57 nvarchar(2048) = NULL,
@notificationText57 nvarchar(2048) = NULL,
@notificationId58 bigint = NULL,
@workItemId58 bigint = NULL,
@deliveryStatus58 tinyint = NULL,
@callerSentTime58 datetime = NULL,
@deliveryStatusInfo58 nvarchar(2048) = NULL,
@notificationText58 nvarchar(2048) = NULL,
@notificationId59 bigint = NULL,
@workItemId59 bigint = NULL,
@deliveryStatus59 tinyint = NULL,
@callerSentTime59 datetime = NULL,
@deliveryStatusInfo59 nvarchar(2048) = NULL,
@notificationText59 nvarchar(2048) = NULL,
@notificationId60 bigint = NULL,
@workItemId60 bigint = NULL,
@deliveryStatus60 tinyint = NULL,
@callerSentTime60 datetime = NULL,
@deliveryStatusInfo60 nvarchar(2048) = NULL,
@notificationText60 nvarchar(2048) = NULL,
@notificationId61 bigint = NULL,
@workItemId61 bigint = NULL,
@deliveryStatus61 tinyint = NULL,
@callerSentTime61 datetime = NULL,
@deliveryStatusInfo61 nvarchar(2048) = NULL,
@notificationText61 nvarchar(2048) = NULL,
@notificationId62 bigint = NULL,
@workItemId62 bigint = NULL,
@deliveryStatus62 tinyint = NULL,
@callerSentTime62 datetime = NULL,
@deliveryStatusInfo62 nvarchar(2048) = NULL,
@notificationText62 nvarchar(2048) = NULL,
@notificationId63 bigint = NULL,
@workItemId63 bigint = NULL,
@deliveryStatus63 tinyint = NULL,
@callerSentTime63 datetime = NULL,
@deliveryStatusInfo63 nvarchar(2048) = NULL,
@notificationText63 nvarchar(2048) = NULL,
@notificationId64 bigint = NULL,
@workItemId64 bigint = NULL,
@deliveryStatus64 tinyint = NULL,
@callerSentTime64 datetime = NULL,
@deliveryStatusInfo64 nvarchar(2048) = NULL,
@notificationText64 nvarchar(2048) = NULL,
@notificationId65 bigint = NULL,
@workItemId65 bigint = NULL,
@deliveryStatus65 tinyint = NULL,
@callerSentTime65 datetime = NULL,
@deliveryStatusInfo65 nvarchar(2048) = NULL,
@notificationText65 nvarchar(2048) = NULL,
@notificationId66 bigint = NULL,
@workItemId66 bigint = NULL,
@deliveryStatus66 tinyint = NULL,
@callerSentTime66 datetime = NULL,
@deliveryStatusInfo66 nvarchar(2048) = NULL,
@notificationText66 nvarchar(2048) = NULL,
@notificationId67 bigint = NULL,
@workItemId67 bigint = NULL,
@deliveryStatus67 tinyint = NULL,
@callerSentTime67 datetime = NULL,
@deliveryStatusInfo67 nvarchar(2048) = NULL,
@notificationText67 nvarchar(2048) = NULL,
@notificationId68 bigint = NULL,
@workItemId68 bigint = NULL,
@deliveryStatus68 tinyint = NULL,
@callerSentTime68 datetime = NULL,
@deliveryStatusInfo68 nvarchar(2048) = NULL,
@notificationText68 nvarchar(2048) = NULL,
@notificationId69 bigint = NULL,
@workItemId69 bigint = NULL,
@deliveryStatus69 tinyint = NULL,
@callerSentTime69 datetime = NULL,
@deliveryStatusInfo69 nvarchar(2048) = NULL,
@notificationText69 nvarchar(2048) = NULL,
@notificationId70 bigint = NULL,
@workItemId70 bigint = NULL,
@deliveryStatus70 tinyint = NULL,
@callerSentTime70 datetime = NULL,
@deliveryStatusInfo70 nvarchar(2048) = NULL,
@notificationText70 nvarchar(2048) = NULL,
@notificationId71 bigint = NULL,
@workItemId71 bigint = NULL,
@deliveryStatus71 tinyint = NULL,
@callerSentTime71 datetime = NULL,
@deliveryStatusInfo71 nvarchar(2048) = NULL,
@notificationText71 nvarchar(2048) = NULL,
@notificationId72 bigint = NULL,
@workItemId72 bigint = NULL,
@deliveryStatus72 tinyint = NULL,
@callerSentTime72 datetime = NULL,
@deliveryStatusInfo72 nvarchar(2048) = NULL,
@notificationText72 nvarchar(2048) = NULL,
@notificationId73 bigint = NULL,
@workItemId73 bigint = NULL,
@deliveryStatus73 tinyint = NULL,
@callerSentTime73 datetime = NULL,
@deliveryStatusInfo73 nvarchar(2048) = NULL,
@notificationText73 nvarchar(2048) = NULL,
@notificationId74 bigint = NULL,
@workItemId74 bigint = NULL,
@deliveryStatus74 tinyint = NULL,
@callerSentTime74 datetime = NULL,
@deliveryStatusInfo74 nvarchar(2048) = NULL,
@notificationText74 nvarchar(2048) = NULL,
@notificationId75 bigint = NULL,
@workItemId75 bigint = NULL,
@deliveryStatus75 tinyint = NULL,
@callerSentTime75 datetime = NULL,
@deliveryStatusInfo75 nvarchar(2048) = NULL,
@notificationText75 nvarchar(2048) = NULL,
@notificationId76 bigint = NULL,
@workItemId76 bigint = NULL,
@deliveryStatus76 tinyint = NULL,
@callerSentTime76 datetime = NULL,
@deliveryStatusInfo76 nvarchar(2048) = NULL,
@notificationText76 nvarchar(2048) = NULL,
@notificationId77 bigint = NULL,
@workItemId77 bigint = NULL,
@deliveryStatus77 tinyint = NULL,
@callerSentTime77 datetime = NULL,
@deliveryStatusInfo77 nvarchar(2048) = NULL,
@notificationText77 nvarchar(2048) = NULL,
@notificationId78 bigint = NULL,
@workItemId78 bigint = NULL,
@deliveryStatus78 tinyint = NULL,
@callerSentTime78 datetime = NULL,
@deliveryStatusInfo78 nvarchar(2048) = NULL,
@notificationText78 nvarchar(2048) = NULL,
@notificationId79 bigint = NULL,
@workItemId79 bigint = NULL,
@deliveryStatus79 tinyint = NULL,
@callerSentTime79 datetime = NULL,
@deliveryStatusInfo79 nvarchar(2048) = NULL,
@notificationText79 nvarchar(2048) = NULL,
@notificationId80 bigint = NULL,
@workItemId80 bigint = NULL,
@deliveryStatus80 tinyint = NULL,
@callerSentTime80 datetime = NULL,
@deliveryStatusInfo80 nvarchar(2048) = NULL,
@notificationText80 nvarchar(2048) = NULL,
@notificationId81 bigint = NULL,
@workItemId81 bigint = NULL,
@deliveryStatus81 tinyint = NULL,
@callerSentTime81 datetime = NULL,
@deliveryStatusInfo81 nvarchar(2048) = NULL,
@notificationText81 nvarchar(2048) = NULL,
@notificationId82 bigint = NULL,
@workItemId82 bigint = NULL,
@deliveryStatus82 tinyint = NULL,
@callerSentTime82 datetime = NULL,
@deliveryStatusInfo82 nvarchar(2048) = NULL,
@notificationText82 nvarchar(2048) = NULL,
@notificationId83 bigint = NULL,
@workItemId83 bigint = NULL,
@deliveryStatus83 tinyint = NULL,
@callerSentTime83 datetime = NULL,
@deliveryStatusInfo83 nvarchar(2048) = NULL,
@notificationText83 nvarchar(2048) = NULL,
@notificationId84 bigint = NULL,
@workItemId84 bigint = NULL,
@deliveryStatus84 tinyint = NULL,
@callerSentTime84 datetime = NULL,
@deliveryStatusInfo84 nvarchar(2048) = NULL,
@notificationText84 nvarchar(2048) = NULL,
@notificationId85 bigint = NULL,
@workItemId85 bigint = NULL,
@deliveryStatus85 tinyint = NULL,
@callerSentTime85 datetime = NULL,
@deliveryStatusInfo85 nvarchar(2048) = NULL,
@notificationText85 nvarchar(2048) = NULL,
@notificationId86 bigint = NULL,
@workItemId86 bigint = NULL,
@deliveryStatus86 tinyint = NULL,
@callerSentTime86 datetime = NULL,
@deliveryStatusInfo86 nvarchar(2048) = NULL,
@notificationText86 nvarchar(2048) = NULL,
@notificationId87 bigint = NULL,
@workItemId87 bigint = NULL,
@deliveryStatus87 tinyint = NULL,
@callerSentTime87 datetime = NULL,
@deliveryStatusInfo87 nvarchar(2048) = NULL,
@notificationText87 nvarchar(2048) = NULL,
@notificationId88 bigint = NULL,
@workItemId88 bigint = NULL,
@deliveryStatus88 tinyint = NULL,
@callerSentTime88 datetime = NULL,
@deliveryStatusInfo88 nvarchar(2048) = NULL,
@notificationText88 nvarchar(2048) = NULL,
@notificationId89 bigint = NULL,
@workItemId89 bigint = NULL,
@deliveryStatus89 tinyint = NULL,
@callerSentTime89 datetime = NULL,
@deliveryStatusInfo89 nvarchar(2048) = NULL,
@notificationText89 nvarchar(2048) = NULL,
@notificationId90 bigint = NULL,
@workItemId90 bigint = NULL,
@deliveryStatus90 tinyint = NULL,
@callerSentTime90 datetime = NULL,
@deliveryStatusInfo90 nvarchar(2048) = NULL,
@notificationText90 nvarchar(2048) = NULL,
@notificationId91 bigint = NULL,
@workItemId91 bigint = NULL,
@deliveryStatus91 tinyint = NULL,
@callerSentTime91 datetime = NULL,
@deliveryStatusInfo91 nvarchar(2048) = NULL,
@notificationText91 nvarchar(2048) = NULL,
@notificationId92 bigint = NULL,
@workItemId92 bigint = NULL,
@deliveryStatus92 tinyint = NULL,
@callerSentTime92 datetime = NULL,
@deliveryStatusInfo92 nvarchar(2048) = NULL,
@notificationText92 nvarchar(2048) = NULL,
@notificationId93 bigint = NULL,
@workItemId93 bigint = NULL,
@deliveryStatus93 tinyint = NULL,
@callerSentTime93 datetime = NULL,
@deliveryStatusInfo93 nvarchar(2048) = NULL,
@notificationText93 nvarchar(2048) = NULL,
@notificationId94 bigint = NULL,
@workItemId94 bigint = NULL,
@deliveryStatus94 tinyint = NULL,
@callerSentTime94 datetime = NULL,
@deliveryStatusInfo94 nvarchar(2048) = NULL,
@notificationText94 nvarchar(2048) = NULL,
@notificationId95 bigint = NULL,
@workItemId95 bigint = NULL,
@deliveryStatus95 tinyint = NULL,
@callerSentTime95 datetime = NULL,
@deliveryStatusInfo95 nvarchar(2048) = NULL,
@notificationText95 nvarchar(2048) = NULL,
@notificationId96 bigint = NULL,
@workItemId96 bigint = NULL,
@deliveryStatus96 tinyint = NULL,
@callerSentTime96 datetime = NULL,
@deliveryStatusInfo96 nvarchar(2048) = NULL,
@notificationText96 nvarchar(2048) = NULL,
@notificationId97 bigint = NULL,
@workItemId97 bigint = NULL,
@deliveryStatus97 tinyint = NULL,
@callerSentTime97 datetime = NULL,
@deliveryStatusInfo97 nvarchar(2048) = NULL,
@notificationText97 nvarchar(2048) = NULL,
@notificationId98 bigint = NULL,
@workItemId98 bigint = NULL,
@deliveryStatus98 tinyint = NULL,
@callerSentTime98 datetime = NULL,
@deliveryStatusInfo98 nvarchar(2048) = NULL,
@notificationText98 nvarchar(2048) = NULL,
@notificationId99 bigint = NULL,
@workItemId99 bigint = NULL,
@deliveryStatus99 tinyint = NULL,
@callerSentTime99 datetime = NULL,
@deliveryStatusInfo99 nvarchar(2048) = NULL,
@notificationText99 nvarchar(2048) = NULL) AS
BEGIN
BEGIN TRANSACTION
DECLARE @serverCurrentUtcTime datetime
DECLARE @clockDeltaMS int
DECLARE @serverSentTime datetime
DECLARE @distributionLogId bigint

-- Determine the delta between the caller's clock and the server's.
SELECT @serverCurrentUtcTime = GETUTCDATE()
SELECT @clockDeltaMS = DATEDIFF(MILLISECOND, @callerCurrentUtcTime, @serverCurrentUtcTime)

IF @notificationId0 IS NOT NULL
BEGIN
-- Calculate what the sent time is, relative to the server's clock.
IF @callerSentTime0 IS NOT NULL
SET @serverSentTime = DATEADD(MILLISECOND, @clockDeltaMS, @callerSentTime0)
ELSE
SET @serverSentTime = NULL

-- Update the row in the notifications table, recording the sent time as relative to
-- the server's clock.
UPDATE "NSESSNotificationsNotifications"
SET DeliveryStatusCode = @deliveryStatus0,
SentTime = @serverSentTime
WHERE NotificationId = @notificationId0
-- Insert a row in the delivery log, recording the sent time as relative to
-- the server's clock.
INSERT INTO "NSDistributionLog" (NotificationClassId, NotificationId, DistributorWorkItemId, DistributorId, DeliveryRequestTime, SentTime, DeliveryStatusCode, DeliveryStatusInfo, NotificationText, LinkDistributionLogId)
VALUES (1, @notificationId0, @workItemId0, @distributorId, NULL, @serverSentTime, @deliveryStatus0, @deliveryStatusInfo0, @notificationText0, NULL)
SELECT @distributionLogId = @@IDENTITY -- dont know why it needs @@IDENTITY here, @distributionLogId is not used in anywhere. the sproc is already close 3000 lines
END

return -- we are done here ...
IF @notificationId1 IS NOT NULL
BEGIN
--
....
..
<Prev in Thread] Current Thread [Next in Thread>