tag:blogger.com,1999:blog-8737022365274990272024-03-13T06:28:07.567-07:00SharePoint MusingsTom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-873702236527499027.post-42466226863555101292013-02-14T10:39:00.000-08:002013-02-14T10:39:27.151-08:00User Profile Sync Issues - MOSS 2007, SharePointErrors had been popping up in our event log for some time and no resolution had been found by opening tickets with Microsoft so I embarked on another epic analysis and you get the cream off the top.<br />
<br />
<h2 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818508"><span style="color: #4f81bd; font-family: Cambria; font-size: medium;">The user does not exist or is not unique</span></a><o:p></o:p></h2>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="font-family: Calibri;">We were seeing errors in the event viewer that looked like:<o:p></o:p></span></div>
<span style="font-family: 'Calibri','sans-serif'; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">failure trying to synch site <GUID> for ContentDB <GUID> WebApp <GUID>.<span style="mso-spacerun: yes;"> </span>Exception message was The user does not exist or is not unique</span><br />
<br />
<span style="font-family: 'Calibri','sans-serif'; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;">Now right off the bat, I should let you know that we cheat a little and have developed some SQL Server Integration Services (SSIS) scripts that gather all these super helpful guid numbers and link them to fairly useless discripters like database names, web app URLs, site URLs... So I just take the GUID and dump it in my search routine and voila - I know which site this little timer job is harping about. You may need to query (never access the SharePoint database directly... blah blah blah) the objects table in the config database then query the sites table in the content database to find your site.</span><br />
<br />
<span style="font-family: 'Calibri','sans-serif'; font-size: 11pt; line-height: 115%; mso-ansi-language: EN-US; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-language: AR-SA; mso-bidi-theme-font: minor-bidi; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818509"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Analysis:</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
This was likely caused by user IDs that were formatted with an @ symbol instead of the common AD format with the \. <span style="mso-spacerun: yes;"> </span>IE:<span style="mso-spacerun: yes;"> </span><a href="mailto:userid@domain">userid@domain</a> (wrong) vs. domain\userid (right).<span style="mso-spacerun: yes;"> </span>The assumption is that these IDs were migrated in from WSS 2.0.<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
Neither of the supported methods for removing the malformed ID worked:<o:p></o:p></div>
<div class="MsoListParagraphCxSpFirst" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7pt 'Times New Roman';"> </span></span></span>STSADM -o deleteuser –url <siteurl> -userlogin <userid><o:p></o:p></div>
<div class="MsoListParagraphCxSpLast" style="margin: 0in 0in 10pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font: 7pt 'Times New Roman';"> </span></span></span>Using the API: SPUserCollection.Remove(<userid>)<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
The user ID had to be transformed to a supported format before it could be deleted properly from SharePoint using the API.<span style="mso-spacerun: yes;"> </span>The user ID is stored in two places – the UserInfo table and the AllUserData table.<span style="mso-spacerun: yes;"> </span>Site collection users are also members of a list.<span style="mso-spacerun: yes;"> </span>Both locations must be updated with the corrected user ID.<o:p></o:p></div>
<h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818510"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Solution: Transform IDs to Supported Format</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
The following query will transform all malformed IDs to the supported format in all content databases on a database server (test, test, test, test... I recommend testing this in non-prod first. test, test, test):<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">-- These series of statements will transform IDs with @ symbols in them to <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">-- the standard format ms\userid<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">drop</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> #UserIdsSel<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">create</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> #UserIdsSel<span style="color: blue;"> </span><span style="color: grey;">(</span>u_db <span style="color: blue;">varchar</span><span style="color: grey;">(</span>128<span style="color: grey;">),</span> u_siteid <span style="color: blue;">uniqueidentifier</span><span style="color: grey;">,</span> id <span style="color: blue;">bigint</span><span style="color: grey;">,</span> t_login <span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">),</span> u_type <span style="color: blue;">varchar</span><span style="color: grey;">(</span>12<span style="color: grey;">))<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">exec</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: maroon;">sp_msforeachdb</span><span style="color: blue;"> </span>@command1 <span style="color: grey;">=</span> <span style="color: red;">'print "?"; use [?] <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">create table #UserIds (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), u_type varchar(12))<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">insert #UserIds<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">select db_name() as dbname<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>,tp_siteid<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>,tp_ID<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>,''<YOURDOMAIN>\'' + parsename(replace(tp_login,''@'',''.''),2 )as NewLogin<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>,''@ Removed''<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">from userinfo<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">where tp_ExternalTokenLastUpdated is NULL<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>and tp_deleted = 0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>and tp_domaingroup = 0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>and tp_Login like (''%@%'')<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>update UserInfo set tp_Login = t_login<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>from #UserIds u<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>where tp_SiteID = u.u_siteid <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and tp_ID = id<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">update AllUserData set nvarchar3 = t_login<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>from #UserIds u <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>where u.u_siteid = tp_SiteId <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and u.id = tp_ID <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and tp_contenttype = ''Person'' <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">insert #UserIdsSel<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">select * from #UserIds<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">drop table #UserIds'<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;">select</span><span style="font-family: 'Courier New'; font-size: 10pt; line-height: 115%; mso-no-proof: yes;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> #UserIdsSel</span><o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
Once the IDs have been transformed to a supported format, they can be deleted without error.<span style="mso-spacerun: yes;"> </span>Since they were malformed in the first place, they could not have been used to access SharePoint.</div>
<h2 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818511"><span style="color: #4f81bd; font-family: Cambria; font-size: medium;">Duplicate User IDs in the Site Collection</span></a><o:p></o:p></h2>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
We discovered that there were some site collections with duplicate user ID values.<span style="mso-spacerun: yes;"> </span>The API should prevent this from happening but this is likely due to a migration.<span style="mso-spacerun: yes;"> </span>We seem to be moving corruption from place to place instead of cleaning it up as we go.<o:p></o:p></div>
<h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818512"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Analysis:</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
The STSADM command and the API both operate against the first ID.<span style="mso-spacerun: yes;"> </span>The code assumes the user ID is unique so the second value is missed.<span style="mso-spacerun: yes;"> </span>The duplicate ID needs to be converted to a new value so the API can “see” it and handle it correctly.<o:p></o:p></div>
<h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818513"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Solution: Transform IDs to Unique IDs by Adding ‘zzz’</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
The duplicate ID needs to be transformed to a new value.<span style="mso-spacerun: yes;"> </span>The following query will process any non-deleted, inactive duplicate ID in all content databases on the database server - you may need to go back and reread the assumptions I made if the query doesn't work for you:</div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: green; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">--Transform duplicate ids to new values<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">drop</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> #UserIdsSel<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">create</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: blue;">table</span> #UserIdsSel<span style="color: blue;"> </span><span style="color: grey;">(</span>u_db <span style="color: blue;">varchar</span><span style="color: grey;">(</span>128<span style="color: grey;">),</span> u_siteid <span style="color: blue;">uniqueidentifier</span><span style="color: grey;">,</span> id <span style="color: blue;">bigint</span><span style="color: grey;">,</span> t_login <span style="color: blue;">varchar</span><span style="color: grey;">(</span>255<span style="color: grey;">),</span> u_type <span style="color: blue;">varchar</span><span style="color: grey;">(</span>12<span style="color: grey;">))<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">exec</span><span style="font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"> <span style="color: maroon;">sp_msforeachdb</span><span style="color: blue;"> </span>@command1 <span style="color: grey;">=</span> <span style="color: red;">'print "?"; use [?] <o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">create table #UserIds (u_db varchar(128), u_siteid uniqueidentifier, id bigint, t_login varchar(255), t_type varchar(12))<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">insert #UserIds <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">select db_name() as dbname<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>,u1.tp_siteid<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>,u1.tp_ID<span style="mso-spacerun: yes;"> </span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>,u1.tp_Login + ''zzz''<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>,''Altered''<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">from userinfo u1 with (nolock) inner join<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">userinfo u2 with (nolock) on u1.tp_siteid = u2.tp_siteid <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and u1.tp_login = u2.tp_login <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and u1.tp_id <> u2.tp_id<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">where u1.tp_ExternalTokenLastUpdated is NULL<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>and u1.tp_deleted = 0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>and u1.tp_domaingroup = 0<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">update UserInfo set tp_Login = t_login<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>from #UserIds u<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>where tp_SiteID = u.u_siteid <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and tp_ID = id<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">update AllUserData set nvarchar3 = t_login<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>from #UserIds u <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-spacerun: yes;"> </span>where u.u_siteid = tp_SiteId <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and u.id = tp_ID <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;"><span style="mso-tab-count: 1;"> </span>and tp_contenttype = ''Person'' <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">insert into #UserIdsSel<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">select * from #UserIds<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt; mso-layout-grid-align: none;">
<span style="color: red; font-family: 'Courier New'; font-size: 10pt; mso-no-proof: yes;">drop table #UserIds'</span><o:p></o:p></div>
<h2 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818514"><span style="color: #4f81bd; font-family: Cambria; font-size: medium;">Duplicate Key Value</span></a><o:p></o:p></h2>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
Message: Cannot insert duplicate key row in object 'dbo.UserMemberships' with unique index 'CX_UserMemberships_RecordId_MemberGroupId_SID'. The duplicate key value is (<RecordID>, <MemberGroupID>, <SID>).<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
This error occurred on the Web Front End but actually refers to the SSP database.<span style="mso-spacerun: yes;"> </span>While performing a User Profile Sync, a recursive membership stored procedure is fired off.<span style="mso-spacerun: yes;"> </span>The query used to determine the uniqueness of the new row is different than the unique clustered index resulting in a failure to insert the row.<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
My analysis below returned 10,912 unique RecordIDs out of 175,602 in the UserMemberships table with 120,851 missing a valid recursive membership. All have an SID.<span style="mso-spacerun: yes;"> </span>These 120,851 are cases where we would get the error from the Event Viewer.<span style="mso-spacerun: yes;"> </span>The record count in UserMemberships is 3,583,335.<o:p></o:p></div>
<h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818515"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Analysis:</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
I'm guessing this is due to some sort of corruption in the SSP. I removed one of the failing entries from the UserMemberships table only to have it fail on another entry. I pulled the info from the SSP Database stored procedures and I believe I have found where it occurs:<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
Stored Procedure: membership_updateRecursiveMemberships<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">BEGIN<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 0.5in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">INSERT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">INTO</span> UserMemberships<span style="color: blue;"> </span><span style="color: grey;">(</span>RecordId<span style="color: grey;">,</span> <span style="color: blue;">SID</span><span style="color: grey;">,</span> MemberGroupId<span style="color: grey;">,</span> GroupType<span style="color: grey;">,</span> GroupTitle<span style="color: grey;">,</span> PolicyId<span style="color: grey;">,</span> ItemSecurity<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 1in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> IM<span style="color: grey;">.</span>MasterRecordId<span style="color: grey;">,</span> <span style="color: grey;">NULL,</span> IM<span style="color: grey;">.</span>MemberGroupId<span style="color: grey;">,</span> @GroupType_DistributionList<span style="color: grey;">,</span> <span style="color: red;">N''</span><span style="color: grey;">,</span> @DLSourceId<span style="color: grey;">,</span> @DefaultItemPrivacy<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 1in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt;"> #MembershipsFromImport IM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 1in;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">LEFT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> UserMemberships UM <span style="color: blue;">ON</span> UM<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> IM<span style="color: grey;">.</span>MasterRecordID <span style="color: grey;">AND</span> UM<span style="color: grey;">.</span>MemberGroupId <span style="color: grey;">=</span> IM<span style="color: grey;">.</span>MemberGroupId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 1in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">WHERE</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UM<span style="color: grey;">.</span>MemberGroupId <span style="color: grey;">IS</span> <span style="color: grey;">NULL<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt 0.5in;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SET</span><span style="font-family: 'Courier New'; font-size: 10pt;"> @ErrorCode <span style="color: grey;">=</span> <span style="color: magenta;">@@Error<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">END<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: #1f497d;">This query defines what to insert into the table:</span><span style="color: #1f497d; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-hansi-font-family: Calibri;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DISTINCT</span> U<span style="color: grey;">.</span>MasterRecordId<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>[Id]<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>SourceReference<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>cs_SourceReference<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt;"> #UsersToConsider U <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> UserMemberOf V <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> U<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> V<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MemberGroup G <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">,</span> <span style="color: blue;">INDEX</span><span style="color: grey;">=</span>IX_MemberGroup_Source_SourceReference<span style="color: grey;">)</span> <span style="color: blue;">ON</span> V<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">AND</span> V<span style="color: grey;">.</span>SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>SourceReference <span style="color: grey;">AND</span> G<span style="color: grey;">.Source</span> <span style="color: grey;">=</span> @DLSourceId <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MembershipRecursive R <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> G<span style="color: grey;">.</span>[Id] <span style="color: grey;">=</span> R<span style="color: grey;">.</span>GroupId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MemberGroup G2 <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> R<span style="color: grey;">.</span>ParentGroupId <span style="color: grey;">=</span> G2<span style="color: grey;">.</span>[Id]<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
So I did some research on how I might be able to find some corruption. I pulled the values above and did a union all on UserMemberships to see the differences:<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DISTINCT</span> U<span style="color: grey;">.</span>MasterRecordId<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>[Id]<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>SourceReference<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>cs_SourceReference<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UserProfile_Full U <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> UserMemberOf V <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> U<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> V<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MemberGroup G <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">,</span> <span style="color: blue;">INDEX</span><span style="color: grey;">=</span>IX_MemberGroup_Source_SourceReference<span style="color: grey;">)</span> <span style="color: blue;">ON</span> V<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">AND</span> V<span style="color: grey;">.</span>SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>SourceReference <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MembershipRecursive R <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> G<span style="color: grey;">.</span>[Id] <span style="color: grey;">=</span> R<span style="color: grey;">.</span>GroupId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">INNER</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">JOIN</span> MemberGroup G2 <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> R<span style="color: grey;">.</span>ParentGroupId <span style="color: grey;">=</span> G2<span style="color: grey;">.</span>[Id]<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">where</span><span style="font-family: 'Courier New'; font-size: 10pt;"> U<span style="color: grey;">.</span>RecordID <span style="color: grey;">=</span> 321984<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">union</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">all<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UM<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">,</span>UM<span style="color: grey;">.</span>MemberGroupId <span style="color: blue;">as</span> id<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">,NULL<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">,NULL<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">from</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UserMemberships UM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">where</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UM<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> 321984<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">order</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">by</span> id <span style="color: blue;">asc</span></span><span style="color: #1f497d; mso-ascii-font-family: Calibri; mso-bidi-font-family: Calibri; mso-hansi-font-family: Calibri;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
When I performed this query, I found that the "valid" user memberships had a record in both cases. However, the "invalid" memberships were missing a row from the first case.<o:p></o:p></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
So I wrote another query to find all "invalid" memberships in the SSP and I was blown away by how many hits I had:<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">drop</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">table</span> #groupMembership<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">create</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">table</span> #groupMembership<span style="color: blue;"> </span><span style="color: grey;">(<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> record <span style="color: blue;">bigint</span><span style="color: grey;">,<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> mgroup <span style="color: blue;">bigint<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">insert</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">into</span> #groupMembership <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SELECT</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">DISTINCT</span> U<span style="color: grey;">.</span>MasterRecordId<span style="color: grey;">,</span> G2<span style="color: grey;">.</span>[Id]<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">FROM</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UserProfile_Full U <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span> UserMemberOf V <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> U<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> V<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span> MemberGroup G <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">,</span> <span style="color: blue;">INDEX</span><span style="color: grey;">=</span>IX_MemberGroup_Source_SourceReference<span style="color: grey;">)</span> <span style="color: blue;">ON</span> V<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>cs_SourceReference <span style="color: grey;">AND</span> V<span style="color: grey;">.</span>SourceReference <span style="color: grey;">=</span> G<span style="color: grey;">.</span>SourceReference <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span> MembershipRecursive R <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> G<span style="color: grey;">.</span>[Id] <span style="color: grey;">=</span> R<span style="color: grey;">.</span>GroupId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span> MemberGroup G2 <span style="color: blue;">WITH </span><span style="color: grey;">(</span><span style="color: blue;">NOLOCK</span><span style="color: grey;">)</span> <span style="color: blue;">ON</span> R<span style="color: grey;">.</span>ParentGroupId <span style="color: grey;">=</span> G2<span style="color: grey;">.</span>[Id]<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select</span><span style="font-family: 'Courier New'; font-size: 10pt;"> um<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">,</span><span style="color: magenta;">sum</span><span style="color: grey;">(</span><span style="color: blue;">case</span> <span style="color: blue;">when</span> <span style="color: blue;">SID</span> <span style="color: grey;">IS</span> <span style="color: grey;">NULL</span> <span style="color: blue;">then</span> 1 <span style="color: blue;">else</span> 0 <span style="color: blue;">end</span><span style="color: grey;">)</span> NULLSid<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">,</span><span style="color: magenta;">sum</span><span style="color: grey;">(</span><span style="color: blue;">case</span> <span style="color: blue;">when</span> <span style="color: blue;">SID</span> <span style="color: grey;">IS</span> <span style="color: grey;">null</span> <span style="color: blue;">then</span> 0 <span style="color: blue;">else</span> 1 <span style="color: blue;">end</span><span style="color: grey;">)</span> WithSid<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">from</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UserMemberships UM <span style="color: blue;">with </span><span style="color: grey;">(</span><span style="color: blue;">nolock</span><span style="color: grey;">)</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">where</span><span style="font-family: 'Courier New'; font-size: 10pt;"> um<span style="color: grey;">.</span>id <span style="color: grey;">not</span> <span style="color: grey;">in</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select</span><span style="font-family: 'Courier New'; font-size: 10pt;"> um<span style="color: grey;">.</span>id <o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">from</span> UserMemberships UM <span style="color: blue;">with </span><span style="color: grey;">(</span><span style="color: blue;">nolock</span><span style="color: grey;">)</span> <span style="color: grey;">inner</span> <span style="color: grey;">join<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: 'Courier New'; font-size: 10pt;"> #groupMembership gm <span style="color: blue;">on</span> UM<span style="color: grey;">.</span>RecordId <span style="color: grey;">=</span> gm<span style="color: grey;">.</span>record <span style="color: grey;">and</span> UM<span style="color: grey;">.</span>MemberGroupId <span style="color: grey;">=</span> gm<span style="color: grey;">.</span>mgroup<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">and</span><span style="font-family: 'Courier New'; font-size: 10pt;"> UM<span style="color: grey;">.</span>RecordId <span style="color: grey;"><></span> <span style="color: grey;">-</span>1<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">group</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">by</span> UM<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">order</span><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">by</span> UM<span style="color: grey;">.</span>RecordId<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
<br /></div>
<h3 style="margin: 10pt 0in 0pt;">
<a href="http://www.blogger.com/null" name="_Toc347818516"><span style="color: #4f81bd; font-family: Cambria; font-size: small;">Likely Solution:<span style="mso-spacerun: yes;"> </span>Delete Database Sync Information</span></a><o:p></o:p></h3>
<div class="MsoNormal" style="margin: 0in 0in 10pt;">
All memberships that seemed to be causing failures were manually deleted with another series of SQL queries but the correct method is likely the stsadm sync command.<span style="mso-spacerun: yes;"> </span>Microsoft Premier support claims that the sync command will clear the SSP of these relationships and cause them to be recreated on the next firing of the “Profile Synchronization” timer job which by default runs every hour.<span style="mso-spacerun: yes;"> </span>The sync timer frequency can be changed using the -SyncTiming option - M:5 would set it to run every 5 minutes.<o:p></o:p></div>
<h4 style="margin: 10pt 0in 0pt;">
<em><span style="color: #4f81bd;"><span style="font-family: Cambria;">stsadm -o sync -listolddatabases 0<o:p></o:p></span></span></em></h4>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">Shared Service Provider SharedServices1</span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;"></span><span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">ID: e51f0d3e-85cc-498a-b330-04a2ccfc1086<span style="mso-spacerun: yes;"> </span>Synchronized: 2/5/2013 8:55:06 AM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">ID: 401a8778-32de-4836-82db-085191159ce7<span style="mso-spacerun: yes;"> </span>Synchronized: 2/5/2013 8:55:07 AM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">ID: ea06156e-27a5-4bb5-a3c5-0ae046083930<span style="mso-spacerun: yes;"> </span>Synchronized: 2/5/2013 8:55:06 AM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">ID: d82336e9-71c1-40eb-8c41-10793f7100db<span style="mso-spacerun: yes;"> </span>Synchronized: 1/22/2013 8:50:07 AM<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">ID: 5f93b1cd-0510……<o:p></o:p></span></div>
<h4 style="margin: 10pt 0in 0pt;">
<em><span style="color: #4f81bd;"><span style="font-family: Cambria;">stsadm -o sync -deleteolddatabases 0<o:p></o:p></span></span></em></h4>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">Deleted sync information for DB e51f0d3e-85cc-498a-b330-04a2ccfc1086<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">Deleted sync information for DB 401a8778-32de-4836-82db-085191159ce7<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="color: grey; mso-themecolor: background1; mso-themeshade: 128;">Deleted sync information…..<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<br /></div>
</span>Tom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.com0tag:blogger.com,1999:blog-873702236527499027.post-71621987052774823292013-01-09T19:36:00.002-08:002013-01-10T05:39:06.329-08:00SharePoint Scheduled Alerts Deconstructed<h4>
This post contains:</h4>
<ul>
<li>Links To Other Alert Troubleshooting Articles</li>
<li>Why Scheduled Alerts Mysteriously Stop Working</li>
<li>What The Immediate Alerts Timer Job Does (Five Stored Procedures)</li>
<li>How To Find Alerts That Didn't Fire</li>
<li>The Moral Of The Story </li>
</ul>
I've spent the past two weeks scouring the internet, tracing SQL, extracting table DDL and stored procedures from SharePoint content databases to finally crack the scheduled alert code. I've generated some SQL queries you can use to find failing alerts, force alerts to re-fire, and determine the type of each alert.<br />
<br />
The SharePoint scheduled alert design hasn't changed much from WSS 3.0. This analysis applies to WSS 3.0, MOSS 2007, SharePoint 2010 and I suspect SharePoint 2013 but haven't verified it.
All my research and data analysis have lead to four words:<br />
<br />
<span style="background-color: orange;">Synchronize your server times.</span><br />
<br />
If your database server and WFEs are not sync'd to the time server, your scheduled alerts will fail. No setting changes, property changes, timer restarts, moving DB processing to another WFE... or whatever will fix your problem. And the next time you do a reboot, time will sync up and things will start working again. <b>Maddening.</b><br />
<h3>
</h3>
<h3>
Links To Other Alert Troubleshooting Articles</h3>
<br />
I'm going to start with credit to some of the posts that helped me along before I get into my stuff.
<br />
<ul>
<li>If Scheduled alerts fail <a href="http://sbelskiy.wordpress.com/2011/08/18/alerts-dont-work-after-migrate-from-2007-to-2010-sharepoint/">after a migration.</a>
</li>
<li>How to change the <a href="http://sadomovalex.blogspot.com/2012/06/how-to-trigger-and-test-daily-alerts-in.html">Scheduled alert time.</a>
</li>
<li>The humorous truth about the <a href="http://chrisdomino.com/blog/post/The-Truth-About-How-Daily-SharePoint-Alerts-Actually-Work">depricated job-daily-alerts property.</a> As well as which timer job runs scheduled alerts (Immediate Alerts), which WFE processes which DB (select * from timerlock), and some other issues I didn't encounter as well as customization.
</li>
<li>Some additional things to check when <a href="http://blogs.technet.com/b/harikumh/archive/2008/05/25/troubleshooting-alerts.aspx">troubleshooting alerts.</a> (I just discovered the server sync time issue in a footnote at the bottom of this article. I decided to make it the star of the show.) </li>
<li>Customizing the alert <a href="http://dirkvandenberghe.com/2008/01/08/how-to-change-the-alert-email-notification-message-for-announcements.html">message templates.</a>
</li>
<li>This incredibly detailed post (or two) with <a href="http://blogs.technet.com/b/steve_chen/archive/2009/11/20/alerts-in-sharepoint-troubleshooting-moss-wss.aspx">troubleshooting steps and database tables.</a>
</li>
<li>And another detailed post <a href="http://blogs.msdn.com/b/vedvyas/archive/2009/09/02/troubleshooting-moss-alerts.aspx">here.</a></li>
</ul>
I can sum most of this up fairly quickly.<br />
<ul>
<li>Scheduled alerts are run via the Immediate Alerts timer job.</li>
<li>The Timerlock table in each database tells you which WFE runs timer jobs for it.</li>
<li>The property "alerts-enabled" must be set to true for each URL.</li>
<li>The property "job-immediate-alerts" tells you when the job runs, typically "every 5 minutes between 0 and 59".</li>
<li>Daily and weekly alert subscriptions are both stored in the SchedSubscriptions table - NotifyFreq column value of 1 = daily, 2 = weekly.</li>
<li>All activity on the site is stored in the EventCache table.</li>
<li>When a subscription is set on an EventCache row, an EventLog entry is created the next time the "Immediate Alerts" job runs from the timer.</li>
<li>Since immediate alerts fire immediately (hence the name) there is no need to store up alerts for a daily or weekly summary.</li>
</ul>
<h3>
</h3>
<h3>
Why Scheduled Alerts Mysteriously Stop Working </h3>
<br />
This is where we kept getting lost. There was nothing wrong with any part of the email or event processing architecture. Immediate alerts worked fine, scheduled alerts didn't go out. We needed a deeper understanding of what was really happening.<br />
<ul>
<li>Scheduled alert subscriptions require an intersection entity (many to many relationship) and use the EventSubsMatches table to join on the EventCache Event ID and the SchedSubscriptions Subscription ID.</li>
<li>When the "Immediate Alerts" timer runs and the SchedSubscription NotifyTime has been past by the <b><i>SQL server </i></b>current time, all alerts joined to the subscription EventsSubsMatches table are summarized and sent out to the subscriber.</li>
<li>The processed rows are removed from EventSubsMatches.</li>
<li>The subscriptions where the NotifyTime has been passed by the <b><i>WFE time </i></b>are incremented by one unit (day, week). </li>
</ul>
Notice that the alerts are processed using the <b><i>SQL server </i></b>time and the subscriptions are incremented by the <b><i>WFE time</i></b>? If your SQL server time is running behind the WFE, it will fail to find any alerts to process (less than 24 hours) and then another stored procedure comes along and pushes the subscription forward like it was processed. This is why my subscriptions weren't going out.<br />
<br />
If you turn on verbose for the Timer ULS log, you will see that the process is running fine but not finding anything to send out. You watch your logs and you capture:<br />
<blockquote class="tr_bq">
<span style="font-family: "Courier New", Courier, monospace;">Begin invoke timer job Immediate Alerts, id {81E07A90-327B-471A-A3A6-7A7672F1D905}, DB {7ABFF5BD-7E23-43FE-B399-F6980AA3E954}</span></blockquote>
It's working! You say to yourself...<br />
<blockquote class="tr_bq">
<span style="font-family: "Courier New",Courier,monospace;">AlertsJob loaded 0 of 0 event data records<br />AlertsJob loaded 0 of 0 subscription records<br />Alertsjob results for immediate delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup<br />Alertsjob results for scheduled delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup<br />AlertsJob processed 0 daily notifications in 0 digests, sent 0 emails, failed to send 0 emails<br />AlertsJob processed 0 weekly notifications in 0 digests, sent 0 emails, failed to send 0 emails</span></blockquote>
Wait, I know I subscribed to alerts and I SHOULD have seen some! Why does it say "0" everywhere?<br />
<h3>
</h3>
<h3>
What The Immediate Alerts Timer Job Does</h3>
<br />
Time to fire up the SQL Profiler and run a trace. I've captured the stored procedures that the timer job uses. Now we can really see what goes on.<br />
<br />
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
proc_GetEventDataAndSubscriptionFilters</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
This takes entries from EventCache table, processes immediate alerts</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Updates EventLog</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Updates EventBatch with timestamp and last processed EventCache Item ID</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Nulls out the EventData and ACL columns in EventCache</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
proc_EnumSubscribedSites</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Pulls a list of Site GUIDs that have subscriptions</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
<span style="font-weight: bold;">proc_MatchSchedSubscriptions</span></div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
<span style="font-weight: bold;">For each site with subscriptions, pull the list of scheduled subscriptions that should fire</span></div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.75in;">
<span style="font-weight: bold;">This one has a where clause that blocks my failed subscriptions</span></div>
<div style="font-family: 'Courier New'; font-size: 10pt; margin: 0in 0in 0in 0.75in;">
<span style="color: red; font-weight: bold;">EventLog.EventTime</span><span style="font-weight: bold;"> </span><span style="color: grey; font-weight: bold;">>=</span><span style="font-weight: bold;"> </span><span style="color: #00b050; font-weight: bold;">@EventTime - CASE</span></div>
<div style="font-family: 'Courier New'; font-size: 10pt; margin: 0in 0in 0in 0.75in;">
<span style="font-weight: bold;"> </span><span style="color: blue; font-weight: bold;">WHEN </span><span style="color: grey; font-weight: bold;">(</span><span style="font-weight: bold;">SchedSubscriptions</span><span style="color: grey; font-weight: bold;">.</span><span style="font-weight: bold;">NotifyFreq </span><span style="color: grey; font-weight: bold;">=</span><span style="font-weight: bold;"> 1</span><span style="color: grey; font-weight: bold;">)</span></div>
<div style="font-family: 'Courier New'; font-size: 10pt; margin: 0in 0in 0in 0.75in;">
<span style="font-weight: bold;"> </span><span style="color: blue; font-weight: bold;">THEN</span><span style="font-weight: bold;"> 1.0</span></div>
<div style="font-family: 'Courier New'; font-size: 10pt; margin: 0in 0in 0in 0.75in;">
<span style="font-weight: bold;"> </span><span style="color: blue; font-weight: bold;">ELSE</span><span style="font-weight: bold;"> 7.0</span></div>
<div style="font-family: 'Courier New'; font-size: 10pt; margin: 0in 0in 0in 0.75in;">
<span style="font-weight: bold;"> </span><span style="color: blue; font-weight: bold;">END</span></div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
proc_DeleteEventLog</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
For each site collection, delete from EventSubsMatchs</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.75in;">
Where EventLog.EventTime < current time</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Delete from EventLog</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in;">
proc_UpdateSchedSubscriptionTimes</div>
<div style="font-family: Calibri; font-size: 11pt; margin: 0in 0in 0in 0.375in;">
Adds either a day or a week to NotifyTime and NotifyTimeUTC </div>
<br />
As you can see, I tested all the where clauses for proc_MatchSchedSubscriptions and found that this particular one was selecting against my scheduled alerts. <br />
<br />
Then, proc_UpdateSchedSubscriptionTimes procedure runs after everything - there is no two phased commit, no logic to ensure it didn't miss anything - just an increment by one to get ready for the next summary.<br />
This is probably a good time to say:<br />
<br />
<div style="text-align: center;">
<span style="background-color: yellow;">"Microsoft doesn't support any manual manipulation of the SharePoint content databases and recommends only using the API." </span> </div>
<br />
So, proceed at your own risk :D.<br />
<h3>
</h3>
<h3>
How To Find Alerts That Didn't Fire</h3>
<br />
Open up your SQL Management Studio, connect to your data server, and <a href="http://spczar.blogspot.com/2013/01/sharepoint-alerts-failing-alerts-all.html">run this query</a><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span></span></span><br />
<span style="color: blue; font-size: x-small;">
</span>
Look at your results tab and you will see all the databases where scheduled alerts that should have been sent - weren't. You will also see another curiosity - <span style="background-color: lime;">alerts with no e-mail addresses.</span> Why would those be there? You can't send an alert unless you have an e-mail address right? <b>This cracks me up</b>. You can create an alert in the GUI even if your site collection user profile doesn't have an e-mail address. However, the stored procedure where clause in proc_MatchSchedSubscriptions blocks those entries. The user THINKS they are going to get alerts, nothing tells them they won't on the front end, and <i>SharePoint just ignores them on the back end. </i>Isn't that nice?<i><br /></i><br />
<br />
Ok, you now have database names with failing alerts, now open a new query window and <a href="http://spczar.blogspot.com/2013/01/sharepoint-alerts-failing-alerts-single.html">run all these queries</a> at once against the DB in question. This is an older screen shot:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-lKqvx-1rYfY/UO4yRer8hSI/AAAAAAAAFk8/STr43qBnBUU/s1600/QueryResults.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="366" src="http://2.bp.blogspot.com/-lKqvx-1rYfY/UO4yRer8hSI/AAAAAAAAFk8/STr43qBnBUU/s640/QueryResults.png" width="640" /></a></div>
<span style="font-size: x-small;"> </span> <br />
Hmmm, you say. What next? Well, I found out that if you push the Schedule back one unit (day, week), it will fire again the next time "Immediate Alerts" timer runs. Also, if there is no e-mail address, you can blow them away (UNSUPPORTED!!). <a href="http://spczar.blogspot.com/2013/01/sharepoint-alerts-force-scheduled.html">If you want to try your luck, you can use these queries.</a> I found this would fire off my alerts 90% of the time but they would fail again on the next rotation - because the server times were off.<br />
<br />
<h2>
The moral of this story: </h2>
<h2>
</h2>
<h2>
"Make sure all your system times are in sync and you can use the summary queries I provided to watch your alerts magically correct themselves as they cross the immediate alert timer boundary."</h2>
Tom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.com7tag:blogger.com,1999:blog-873702236527499027.post-18032013434492867872013-01-09T19:28:00.001-08:002013-01-09T19:28:44.865-08:00SharePoint Alerts - Force Scheduled Alerts, Remove Bad Alerts--Push All Old Subscriptions<br />update SchedSubscriptions <br /> SET<br /> NotifyTime = CASE WHEN (NotifyTime IS NOT NULL) THEN NotifyTime ELSE @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END,<br /> NotifyTimeUTC = CASE WHEN (NotifyTimeUTC IS NOT NULL) THEN NotifyTimeUTC ELSE @Now END - CASE WHEN (NotifyFreq = 1) THEN 1.0 ELSE 7.0 END <br /> where Id in (select ss.Id<br /> FROM dbo.EventSubsMatches AS esm LEFT OUTER JOIN<br /> dbo.EventCache AS ec ON esm.EventId = ec.Id LEFT OUTER JOIN<br /> dbo.SchedSubscriptions AS ss ON esm.SubId = ss.Id LEFT OUTER JOIN<br /> dbo.EventLog AS el ON esm.EventId = el.Id <br /> WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1.0 else 7.0 END))));<br /> <br />--Delete subscriptions with missing e-mail addresses<br />SET NOCOUNT ON;<br />DECLARE @sub uniqueidentifier;<br />DECLARE sub_cursor CURSOR FOR<br /> select ss.Id<br /> from UserInfo as ui inner join<br /> SchedSubscriptions as ss on ui.tp_ID = ss.UserId<br /> where ss.UserEmail = N'';<br />OPEN sub_cursor<br /><br />FETCH NEXT FROM sub_cursor<br />INTO @sub<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> delete from SchedSubscriptions where Id = @sub<br /> delete from EventSubsMatches where SubId = @sub<br /> PRINT 'Deleting subscription with missing e-mail address ' + CAST(@sub as varchar(40))<br /> FETCH NEXT FROM sub_cursor<br /> INTO @sub<br />END<br />CLOSE sub_cursor;<br />DEALLOCATE sub_cursor;Tom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.com0tag:blogger.com,1999:blog-873702236527499027.post-8259034909852616552013-01-09T19:11:00.002-08:002013-01-09T19:12:54.337-08:00SharePoint Alerts - Failing Alerts, All Databases Summary Queryexec sp_msforeachdb @command1 = 'print "?"; use [?] select db_name() as dbname,<br /> dbo.Webs.FullUrl<br /> ,sum(CASE dbo.SchedSubscriptions.NotifyFreq<br /> WHEN 1 THEN 1<br /> END) as Daily<br /> ,sum(CASE dbo.SchedSubscriptions.NotifyFreq<br /> WHEN 2 THEN 1<br /> END) as Weekly<br /> ,SUM(CASE dbo.SchedSubscriptions.UserEmail<br /> WHEN N'''' THEN 1<br /> ELSE 0<br /> END) MissingEmail<br /> ,MAX(DATEDIFF(hour,EventLog.EventTime,SchedSubscriptions.NotifyTimeUTC)) as OldestHours<br />FROM dbo.EventCache with (nolock) INNER JOIN<br /> dbo.EventLog with (nolock) ON dbo.EventCache.Id = dbo.EventLog.Id INNER JOIN<br /> dbo.EventSubsMatches with (nolock) ON dbo.EventCache.Id = dbo.EventSubsMatches.EventId INNER JOIN<br /> dbo.SchedSubscriptions with (nolock) ON dbo.EventSubsMatches.SubId = dbo.SchedSubscriptions.Id INNER JOIN<br /> dbo.Webs with (nolock) ON dbo.EventCache.WebId = dbo.Webs.Id<br />WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1.0 else 7.0 END)))<br />GROUP BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl<br />ORDER BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl 'Tom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.com0tag:blogger.com,1999:blog-873702236527499027.post-31143883234585739562013-01-09T19:08:00.000-08:002013-01-09T19:08:25.458-08:00SharePoint Alerts - Failing Alerts Single DB Query--Scheduled Alerts Query<br />-- Tom Sheffrey 1/3/2013<br />-- Find alerts that should have fired<br />-- Check for missing Join values<br /><br />-- DB and Server Info<br />select DB_NAME() as DatabaseName <br /> ,getdate() as ServerTimeStamp <br /> ,LockedBy <br /> ,dateadd(hour, -6,LockedTime) as LockedTimeCST from timerlock;<br /><br />-- Failing alert summary<br />Select dbo.Webs.FullUrl<br /> ,count(CASE dbo.SchedSubscriptions.NotifyFreq<br /> WHEN 1 THEN 1<br /> END) as Daily<br /> ,count(CASE dbo.SchedSubscriptions.NotifyFreq<br /> WHEN 2 THEN 2<br /> END) as Weekly<br /> ,SUM(CASE dbo.SchedSubscriptions.UserEmail<br /> WHEN N'' THEN 1<br /> ELSE 0<br /> END) MissingEmail<br /> ,MAX(DATEDIFF(hour,EventLog.TimeLastModified,SchedSubscriptions.NotifyTimeUTC)) as OldestHours<br />FROM dbo.EventCache with (nolock) INNER JOIN<br /> dbo.EventLog with (nolock) ON dbo.EventCache.Id = dbo.EventLog.Id INNER JOIN<br /> dbo.EventSubsMatches with (nolock) ON dbo.EventCache.Id = dbo.EventSubsMatches.EventId INNER JOIN<br /> dbo.SchedSubscriptions with (nolock) ON dbo.EventSubsMatches.SubId = dbo.SchedSubscriptions.Id INNER JOIN<br /> dbo.Webs with (nolock) ON dbo.EventCache.WebId = dbo.Webs.Id<br />WHERE (DATEDIFF(Hour,EventLog.eventtime,SchedSubscriptions.NotifyTimeUTC) > (24 * (CASE SchedSubscriptions.notifyfreq when 1 then 1 else 7 END)))<br />GROUP BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl<br />ORDER BY dbo.SchedSubscriptions.NotifyFreq, dbo.Webs.FullUrl;<br />
<br />
-- Entries with NULL subscriptions<br />select COUNT(eventsubsmatches.eventid) as NullMatches<br /> from EventSubsMatches left outer join <br /> SchedSubscriptions on EventSubsMatches.SubId = SchedSubscriptions.Id <br /> where SchedSubscriptions.Id is NULL;<br />-- Detail records with EventType and Filter analysis<br />select w.FullUrl as WebUrl<br /> ,ec.SiteId<br /> ,esm.eventid<br /> ,esm.subid as SubscriptionID<br /> ,ec.ItemName<br /> ,ec.ItemFullUrl<br /> ,el.eventtime as EventTime<br /> ,ss.NotifyTime as SubscriptionNotifyTime<br /> ,w.TimeZone<br /> ,DATEDIFF(hour,ss.NotifyTime,ss.NotifyTimeUTC) as OffsetHour<br /> ,DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) as Hours<br /> ,CASE ss.NotifyFreq<br /> when 1 THEN 'Daily'<br /> when 2 THEN 'Weekly'<br /> END as Frequency<br /> ,ss.UserEmail<br /> ,CASE ss.AlertType<br /> when 0 then 'List'<br /> when 1 then 'Item'<br /> else CAST(ss.AlertType as varchar(8))<br /> END as AlertType<br /> ,CASE ss.EventType<br /> when -1 THEN 'All Changes'<br /> when 1 then 'New items are added'<br /> when 2 then 'Existing items are modified'<br /> when 4 then 'Items are deleted'<br /> when 4080 then 'Web discussion updates'<br /> else 'Unknown'<br /> END as 'Change Type'<br /> ,CASE ss.Filter<br /> when N'' then 'Anything Changes'<br /> when '%Editor/New%' then 'Someone else changes a document'<br /> when '%Author%' then 'Someone else changes a document owned by me'<br /> when '%Editor/Old%' then 'Someone else changes a document modified by me'<br /> else 'Unknown'<br /> END as 'Send alerts for these changes'<br />FROM dbo.EventSubsMatches AS esm LEFT OUTER JOIN<br /> dbo.EventCache AS ec ON esm.EventId = ec.Id LEFT OUTER JOIN<br /> dbo.SchedSubscriptions AS ss ON esm.SubId = ss.Id LEFT OUTER JOIN<br /> dbo.EventLog AS el ON esm.EventId = el.Id LEFT OUTER JOIN<br /> dbo.Webs AS w ON ec.WebId = w.Id<br />WHERE (DATEDIFF(hour,el.eventtime,ss.NotifyTimeUTC) > (24 * (CASE ss.notifyfreq when 1 then 1.0 else 7.0 END)))<br /> or WebUrl is NULL<br />order by hours desc;<br />Tom Sheffreyhttp://www.blogger.com/profile/17213637063568743144noreply@blogger.com1