KL573991

I'm getting this error in the upgrade.log when attempting to upgrade a site collection from wss2 to wss3. I'm unable to identify what on the site is causing this issue. Any help would be appreciated. Thx. --KL

[SPContentDatabaseSequence] [ERROR] [4/9/2007 7:34:36 AM]: Action 3.0.87.0 of Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence failed.
[SPContentDatabaseSequence] [ERROR] [4/9/2007 7:34:36 AM]: Violation of UNIQUE KEY constraint 'AllUserData_Url'. Cannot insert duplicate key in object

'dbo.AllUserData'.
Violation of UNIQUE KEY constraint 'AllUserData_Url'. Cannot insert duplicate key in object 'dbo.AllUserData'.
The statement has been terminated.
The statement has been terminated.
[SPContentDatabaseSequence] [ERROR] [4/9/2007 7:34:36 AM]: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean

breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet

bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,

DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.Upgrade.UpgradeDiscussionLists.DiscussionBoardConverter.ItemInfoTable.UpdateDiscussionBoardItems(ListInfo list, ISqlSession

session, SqlCommand cmd)
at Microsoft.SharePoint.Upgrade.UpgradeDiscussionLists.DiscussionBoardConverter.ProcessItemBatch(SqlCommand cmdGetItemBatch, SqlCommand

cmdUpdateItemBatch, ListInfo list, SqlGuid& threadId, SqlString& strOrdering, Int32& itemId)
at Microsoft.SharePoint.Upgrade.UpgradeDiscussionLists.DiscussionBoardConverter.ProcessDiscussionBoard(ListInfo list)
at Microsoft.SharePoint.Upgrade.UpgradeDiscussionLists.DiscussionBoardConverter.Run()
at Microsoft.SharePoint.Upgrade.UpgradeDiscussionLists.Upgrade()
at Microsoft.SharePoint.Upgrade.SPActionSequence.Upgrade()
[UpgradeDiscussionLists] [3.0.87.0] [DEBUG] [4/9/2007 7:34:36 AM]: Begin Rollback()
[UpgradeDiscussionLists] [3.0.87.0] [DEBUG] [4/9/2007 7:34:36 AM]: End Rollback()
[UpgradeDiscussionLists] [3.0.87.0] [DEBUG] [4/9/2007 7:34:36 AM]: Begin Dispose()
[UpgradeDiscussionLists] [3.0.87.0] [DEBUG] [4/9/2007 7:34:36 AM]: End Dispose()
[UpgradeDiscussionLists] [3.0.87.0] [DEBUG] [4/9/2007 7:34:36 AM]: Elapsed time: 00:00:01.7980920.



Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Gonzalo Mahave

Hi, i've got the same error and the only way i was able to fix it is that i deleted some records out of the AllUserInfo table. Try deleteing some rows by tp_ContentType, this field tells you which kind of data is stored (announcement, link, etc.)

Probably there should be a better way to do this, but thats the way i found and it took me just a few minutes to solve.

Hope it helps,

Gonzalo





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Greg Limelight

Dude, all I can say is thank you.... I have been working my ass off for two weeks trying to get our WSS 2.0 stuff prepped and ready for the upgrade to WSS 3.0 (we might do an inplace OR database migration) and this little tidbit ended up saving the day. I am working on a manifesto to describe all the painful stuff I had to do to get this to work and I will post it up here for anyone who is getting similar issues.





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Sungsit Lertsintawanon

Hi Gonzalo & Greg,

I found an exact problem while migrating SPS 2003 to MOSS 2007. I went through lot's of errors and fixing it one at a time until getting this problem. I'm very interesting in the way that you solving problem by deleting some tp_ContentType row in AllUserInfo table. But what I found in SharePoint database (I mean DBName_SITE) is there is no AllUserInfo table, the only table that is closet to what you've mention is UserInfo table but again it doesn't have tp_ContentType as well.

The question is which table that you mention in SharePoint database that required to do this fixing method Would you please explain more about this

Best Regards.,

Sungsit





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Gonzalo Mahave

Hey Sungsit.

The first thing you need to understand is that SPS 2003 and MOSS 2007 don't have the same database (i mean they dont have the same set of tables, some are the same, some are new, some were deprecated).


When you start migrating what i found so far is that the migration starts by creating one of the new tables, then tries to move the content (probably within a transaction) and then deletes de old table if the transaction went right. The AllUserInfo is a table from SPS2003 (it was replaced by AllUserData), so if you dont have it anymore it means that the migration process already processed that table and it went ok.

However the error tells you which table (of the new ones) is having the problem when trying to move the content

"SPContentDatabaseSequence] [ERROR] [4/9/2007 7:34:36 AM]: Violation of UNIQUE KEY constraint 'AllUserData_Url'. Cannot insert duplicate key in object

'dbo.AllUserData'."

Thats an example and it says that the problem is given with the AllUserData table.

So, you need to check the error log, then guess which one is the old table that is going to be replaced by that table and go into that table and figuire it out which rows are making the error and delete them (other way is to delete random sets of rows and check if the migration process gets through)

I hope this helps.

Good luck,


Gonzalo





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Sungsit Lertsintawanon

In that case the Migration progress is already been done with error. How can I continue running the migration from that point

BTW, I forgot to tell you that I'm using Database Migration approach which starting with all of these steps:

1. Prescan the SPS 2003 then Backup using SPS Backup/Restore Tools

2. Moving the whole set of backup to a new MOSS 2007 server then restore it on SQL Server Manually

3. Create a New Webapp with dummy database e.g. http://sharepoint with database SharePoint_DUMMY

4. Then run STSADM to delete Content Database with this

"stsadm 每o deletecontentdb 每url http://sharepoint 每databasename SharePoint_DUMMY"

5. And then reattach New Content Database (the old database_SITE from SPS 2003) with

"stsadm 每o addcontentdb 每url http://sharepoint 每databasename SharePoint_WSS_CONTENT"

  • Here is where the error comes: 'Violation of UNIQUE KEY constraint 'AllUserData_Url'. Cannot insert duplicate key in object 'AllUserData'.'

Normally this is where I'm back to Step 4. and starting to delete and reattach it again with Step 5. If you mention that I can fixed it by delete a fault record (at this point the Step 5 is already complete with the error), can I repeat the step 5. without trying step 4.

Anyway, Thank you very much for give me a very good and fast responsed, Gonzalo

Appreciate your help very much

Sungsit





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

girishchitre

Hi ALL

Did you get the solution to the problem you faced, i am getting similar error but not for violation of unique key constraint.

Girish






Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Greg Limelight

Hey guys, been a while since I checked this forum (I always just google to get right to it). Anyway, I have FINALLY got a successful upgrade for ONE out of TWO WSS 2.0 to 3.0 servers. The process is a complete clusterf**k and Microsoft should be ashamed at how hard this is to get done... I actually have an open case with Microsoft's sharepoint support team and they were NO help. The guy I'm working with seems nice, but didn't even understand host headers in IIS and of course didn't understand sharepoint in host header mode... ridiculous. I THINK the issue has to do with discussion boards that have the exact same name/URL. So of course in this case I have 50 sites in one content DB and it would be impossible to find out where the dupes are so following the original posters comments and expanding a little I found dupes. The kicker is, it WORED on the database named Maya. BUT, an inplace upgrade STILL FAILED!!! The only way it did work is using the database migration strategy or the stsadm -o addcontentdb technique. And the utlimate kicker! It's STILL failing on Titan (the notes used below) but it worked on Maya). BTW - I'm seeing a hell of a lot more hits when I google this error than I did even a week ago... maybe MS is waking up Here are my notes and here is one great article which got me on my way:

http://www.kbalertz.com/kb_937290.aspx

Basically, what I ended up doing after getting this error is the following: Search the upgrade.log file immediately after the database migration fails (I delete everything I can in the /logs directory everytime before attempting another upgrade/database migration). So in my example, Titan is the name of the content DB I'm working with and these steps indicate what I'm doing using SQL system manager on that database:

Expand Titan - Tables - AllUserData - Rightclick - Open Table - Query - choose (meaning, check off):

tp_SiteID, tp_ContentType, tp_DirName


Set the critieria column for tp_ContentType to Discussion and then enter and hit the exclamation point to run the query.
The site ID is the guid of the site which will match a URL when reviewing the "Sites" table (in the same content DB), the tp_DirName's returned will show you the full paths to sites that are using the same "Discussion Board" name.

Fix these using Front Page.

TitAN:

tp_SiteId - Use this to compare to the sites table to find the URL/Site
{76AAEF6F-8EE9-42EC-B39B-7540CA76FF9B}
{76AAEF6F-8EE9-42EC-B39B-7540CA76FF9B}
{B8C6032B-96D1-4F89-B7D2-8AD47B09CFDD}
{1EC911C1-D279-4232-90B4-C50912634375}
{C7FB468F-A8B8-401B-900E-E7356107E252}

tp_DirName - Use this to track down the path and change the name using FrontPage (see kbalertz article above)
BusinessServices/HelpDesk/Lists/Help Desk Newsgroup
BusinessServices/HelpDeskCustom/Lists/Help Desk Newsgroup
ITDeveloper/Lists/Help Desk Newsgroup
temp3/Lists/Help Desk Newsgroup
ITDeveloper/Lists/Help Desk Newsgroup

5/22/07 - Results (BEFORE WE MADE OUR CHANGES):
b2bcollaborations.com - BusinessServices/HelpDesk/Lists/Help Desk Newsgroup
b2bcollaborations.com - BusinessServices/HelpDeskCustom/Lists/Help Desk Newsgroup
connect.catalystss.com - ITDeveloper/Lists/Help Desk Newsgroup
sharepointdev.missioncontrol.com - temp3/Lists/Help Desk Newsgroup
oldconnect.catalystss.com - ITDeveloper/Lists/Help Desk Newsgroup

6/22/07 - Results (AFTER WE MADE OUR CHANGES):
tp_DirName
b2bcollaborations.com - BusinessServices/HelpDesk/Lists/Business Services Help Desk Newsgroup
b2bcollaborations.com - BusinessServices/HelpDeskCustom/Lists/Business Services Customer Help Desk Newsgroup
connect.catalystss.com - ITDeveloper/Lists/Connect Help Desk Discussion
sharepointdev.missioncontrol.com - temp3/Lists/Mission Control Help Desk Newsgroup
oldconnect.catalystss.com - ITDeveloper/Lists/Old Connect Help Desk Newsgroup





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Greg Limelight

I figured it out. Microsoft support was no help on this one unfortunately... we both agreed that the issue dealt with similarly named discussion boards as per this article: http://support.microsoft.com/kb/937290 The HUGE problem with this article and the upgrade.log file is that nowhere does it tell you exactly WHICH discussion boards are similar/duplicates and actually causing the issue. Here is what you need to do:

1. Start fresh to copy/delete/move any old log files from C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS so nothing is appended an all is new. You will probably get one error that the newest log file cannot be removed which is fine. Make sure to delete ALL others.

2. run your stsadm -o addcontentdb command again to try and attached the restored v. 2.0 contentdb -- it will fail

3. the upgrade.log file doesn't tell you sh*t except for the Violation of UNIQUE KEY constraint 'AllUserData_Url error(s)

4. you need to go into the other log file which should be written as: servername-date-time.log This is time consuming and excruciating, but if you go through it line by line you are looking for ANYTHING that resembles a recognizable path to a discussion board (basically, anything in English". This might be really hard or really easy to decipher. In our case we are in host header mode with one content DB that has 76 sites within it! Here is the data that I had extracted from my log file:

Recommended PMP Prep Materials
PMP Exam Tips
Lists/Career Development/PMP Exam Tips'

'Project Program Management Best Practices'
'Lists/Career Development Discussion Board/Project Program Manag...'
...ow I studied for (and passed!) the PMP exam
'Lists/Career Development Discussion Board/Cube of Expertise'
You can see that the data is relaly random and it doesn't list the full paths, but I highlighted a possible red flag showing similar naming of discussion boards WITHIN THE SAME site! That is key, it doesn't seem to care about multiple sites with similar discussion board names, but multiple discussion boards in the same sight with similar names. Also, when I'm talking about "names" I'm really referring to the URL that points to the discussion boards in question. So when you go to a discussion board and click on Modify sites views and columns you see the entire URL.

5. Now, the very hard part to all this (and the part Microsoft cannot answer yet) is HOW to find out what site these lists belong to... and here is where all the previous posts come into play.

6. Go into SQL manager - <your content database> - tables - AllUserData and choose:

Open Table - Query - choose (meaning, check off):

tp_SiteID, tp_DirName

In the criteria column you want to paste info that you found in your log file. The only hits I got in my case were when I used

lists/career development - yours will be different. So you add the criteria and hit enter to give it the right syntax and then hit the exclamation point to run the query.

7. What you are interested in is the tp_SiteID. This will give you the GUID of the site where this stuff is located.

8. Then just open the sites table in your database similar to the above and you can query just for tp_SiteID using the guid that you found in the previous query. If you scroll all the way to the end of the resulting rows you will see a column for host header... and bam, that's the offending site.

9. So, in both of my cases instead of trying to use the Frontpage technique in the article to rename the discussion board / URL's I got permission to completely delete those lists/discussion boards (they weren't used in over a year).

10. Finally another production backup/restore to lab/attachcontentdb and it WORKED!!!

I was able to go into central admin, look at the attached content DB and see it had 76 sites... same a production. You can also list the site collections in WSS 3.0 (pretty cool) as well.

11. I then just added host headers for each site I wanted to view and host file entries (because I was in the lab) and bam... everything is viewable.

12. There is more to clean up and images and whatnot might not come over the way the client expects, but that is just part of upgrading... I hope this can help someone here.

BTW - I have a conf call with Microsoft Sharepoint Support to provide this insight to them as well. Maybe they can clean it up a little.





Re: SharePoint - Setup, Upgrade, Administration and Operation Violation of UNIQUE KEY constraint 'AllUserData_Url' on upgrade from wss2 to wss3

Daniel Andre?

Hi to all,
I followed this topic because I had the same problem. But fortunately I found inspecting the class diagram that I forgot somehow a unique key constraint. after deleted that constraint I can insert without any problem(please note that I'm not talkin' about the constraint created in order to make realtionships with other tables).

My advice would be to chek your constraints from diagram class.

Hope this helps,
Daniel