11/26/2024 10:26:15 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general administration topics for SalesLogix (including LAN & remote topics). View the code of conduct for posting guidelines.
|
|
|
|
Usernotification entries with missing ActivityIDs
Posted: 12 Jan 07 1:22 PM
|
fiogf49gjkf0d Version: 6.2
When I query all records in the usernotification table, I'm given something like 300ish. When I refine the query to "select * from usernotification where activityid in (select activityid from activity)" I only return 20.
The Integrity Checker (IC) looks for bad touserid and fromuserid but not if the activity doesn't exist. I have a feeling these are orphaned records and I'm safe to delete them (based on the sentdate) but I just want to make sure. If they are orphaned, they're not included in IC for 6.2 and should be. It could be added to v7's IC if it's not already there. |
|
|
|
Re: Usernotification entries with missing ActivityIDs
Posted: 13 Jan 07 12:26 AM
|
fiogf49gjkf0d Jeremy - they should have a TYPE of "Deleted" - this is a user_confirmation record to let the user know that a prior/booked activity was deleted. They're safe to delete - but, they will be deleted if the user when to confirmation tab, hit Confirm on the confirmation of the deletion (!) |
|
|
|
Re: Usernotification entries with missing ActivityIDs
Posted: 16 Jan 07 1:05 PM
|
fiogf49gjkf0d I've done some more investigating and I've come up with some interesting stuff:
1) The Type is actually "Removed" not deleted. The only types where an activityid is not in the activity table is if it's a Removed or Completed type.
2) There are a number of records with a blank activityid. The majority of which fall during this year, the other in 2005. There seems to be an odd year pattern but that's probably coincidence.
3) The bulk of the records either originate from or were sent to a remote database. A bulk fall within a known sync problem but the rest are suspect. These records are orphaned most likely because on the remote database they've been dealt with but for whatever reason, the host didn't.
4) The primary keys change from a base of "n" to "Q" in January '05. This indicates that the ids are getting created against the "Other" sitekey, not the "User Notification". The date may correspond to when we upgraded from 6.1 to 6.2 but I can't be 100% certain.
5) Almost 100% of the blank activity records start out like this:
6.2.1
39085.5000000116
120
262145
(Account)
(Contact)
(Regarding)
(Space)
(Phone Number)
262145 is an activity/history type. The records are of type completed so this probably matches. 6.2.1 looks like a SLX version, but we were never on 6.2.1, I skipped directly to SP2 when we upgraded and we're on SP3 now with hotfixes (yes, I know it's old but I'm rushing to get us to v7).
6) The dates of all records span both 6.1 and 6.2, so I don't believe a specific version is the culprit.
7) This doesn't seem serious but merely an annoyance. I'm anal so I'll be keeping the records around until I have a better understanding as to why this is happening.
8) This is a very sporatic problem, so profiling would be extremely difficult or I'd have to leave it running for days on a computer that someone actively uses and stays in SLX 24/7. That's not exactly optimal.
Here's the query I've been running:
select * from usernotification where activityid not in (select activityid from activity) order by senddate
I'm gathering my notes on this and working on creating IC entries for this, unless ones already exist. This way I can do a scrub during our upgrade and monitor it with the IC to see if the problem crops back up. Perhaps, if I'm feeling frisky, I can modify the activity screens to do a little behind the scenes logging to try and track it down further, if it becomes that big of an issue.
Thanks again for the help Mike, I didn't want to sacrifice data integrity for my OCD behaviour. I'm wondering if anyone else has a similar problem, or if people even check. I took it for granted that usernotifications were being handled and while technically they seem to be (I'd have people complaining), it may be something that just goes unnoticed until you specifically look for it. |
|
|
|
Re: Usernotification entries with missing ActivityIDs
Posted: 27 Mar 09 1:25 PM
|
I kept the email as a reminder to do something but I didn't notice it until 2 years later when I was recreating a deleted activity. Sad.
The records still exist and have since made new friends. I went ahead and created an IC entry for it by just copying the "Bad FromUserId" entry.
Test name: User Notification Records with Bad Activity IDs (Delete)
Repair type: Delete record
Repair message: Deleted
Select: SELECT ACTIVITYID, SENDDATE, TYPE
From: FROM USERNOTIFICATION
Where: WHERE NOT EXISTS
(SELECT ACTIVITYID FROM ACTIVITY A2
WHERE USERNOTIFICATION.ACTIVITYID = A2.ACTIVITYID) ORDER BY SENDDATE
Repair: DELETE
If you remove senddate from the select statement, remove the order by clause in the where.
I haven't cleaned the data so far so when I ran it I got entries going back to 7/2004, which is precisely the month and almost to the day we upgraded to 6.1 if memory serves. That would correspond to the upgraded 5.2 database missing primary keys in the database itself which gives a sync error due to syncsequencing missing the clustered primary key. I made sure to mirror a blank database in making those primary keys but its possible there are some other constraints on the table causing ours to keep orphans.
It's also entirely possible that there are orphans in every database. The only time I can think of it happening is when an activity record is deleted on either end of the sync as mentioned its supposed to create Deleted/Removed entries. The type is sporatic so there's really nothing indicating how this is happening.
My hope is that this really just affects everyone because it means less work for me in trying to pin it down. I'll setup the IC entry and run it on the Eval database just to see if there's a difference. I know this is old, but if someone out there could add the IC entry and just do a test on your system the results would be greatly appreciated. I won't feel like I'm chasing my tail. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|