SharePoint 2007 : Issues with 'Not Finished' Surveys

Today, I was forced to go into the SharePoint Survey Database to try to "unlock" the not finished surveys.

The problem is due to the misuse of the Survey Button.

End-user think that if we point the fact that the button "FINISH" will really finish the survey, part of them will never click on it, and they'll think that the "SAVE" button will do the same.

At the other end of the chain, I'm facing with the survey result that is not compatible with the number of sites that should be in the survey.

Little search on internet, and this post (http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/b9ecc145-9bfc-42a2-ae5a-77416657ea85/) gave me the missing pieces of my jigsaw.

The SQL items that must be changed to force the saved survey to become as finished survey.

-          Step 1 : write down the Survey Name (for me, I have to do the job 3 times…) :

-          Step 2 : open your SQL Server Management Studio and open you DB

-          Step 3 : in your dbo.Lists view, find your survey(s) name.

Used Query :

SELECT TOP 1000 [tp_WebId]

      ,[tp_ID]

      ,[tp_Title]

      ,[tp_Created]

  FROM [SharePoint_Content_shpSurvey].[dbo].[Lists]

  Where [tp_Title] like '%Upgrade%'

                               Note : I've used the 'like' word as my three surveys had this word in their name.

                Result :

Now, I've my 3 tp_ID related to all my Surveys.

-          Step 4 : Let's check the content of the dbo.UserData

Used Query :

SELECT TOP 1000 [tp_ID]

      ,[tp_Level]

      ,[tp_DeleteTransactionId]

      ,[tp_CheckoutUserId]

      ,[tp_LeafName]

      ,[nvarchar5]

      ,[tp_Modified]

      ,[tp_Created]

      ,[tp_ContentType]

      ,[nvarchar4]

  FROM [SharePoint_Content_shpSurvey].[dbo].[UserData]

  where [tp_ListID] = '90A65233-C431-414D-8B19-F14C20F799EF'

                Result :

In this list, we can see the completed Surveys (green box).

If tp_Level = 1 and tp_CheckoutUserId = NULL, then the survey is "Completed".

If tp_Level = 255 and tp_CheckoutUserId is not null, then you've pointed out the unfinished surveys.

Adding this line in the previous query "and [tp_Level] = 255", will give the surveys to modify : 10 rows to correct.

-          Step 5 : now, we are going to set the tp_Level to 1 and tp_CheckoutUserId to NULL

Used Query :

BEGIN TRAN UPDATE    UserData

                             SET              tp_CheckoutUserId = NULL, tp_Level = 1

                             WHERE     tp_ListId = '90A65233-C431-414D-8B19-F14C20F799EF' AND tp_Level = 255 AND tp_DeleteTransactionId = 0 COMMIT

Now, checking the dbo.UserData entries : rows 14, 17 and 21 are now correct

-          Step 6 : Let's check the content of the dbo.Docs

Used Query :

SELECT TOP 1000 [Id]

      ,[CheckoutUserId]

      ,[CheckoutDate]

      ,[Level]

      ,[DeleteTransactionId]

  FROM [SharePoint_Content_shpPortal].[dbo].[Docs]

  Where [ListID] = '90A65233-C431-414D-8B19-F14C20F799EF'

In the green square, you can find the correct values for CheckoutUserId, CheckoutDate and Level columns.

In the red square, values that shows the unfinished surveys.

-          Step 7 : now, we are going to set the CheckoutUserId, CheckoutDate to NULL, and Level to 1

Used Query :

BEGIN TRAN UPDATE    Docs

                             SET              CheckoutUserId = NULL, CheckoutDate = NULL, [Level] = 1

                             WHERE     ListId = '90A65233-C431-414D-8B19-F14C20F799EF' AND [Level] = 255 AND CheckoutDate IS NOT NULL AND

                                                   DeleteTransactionId = 0 COMMIT

Now, let's check by searching the Id of former unfinished surveys :

As you can see, all the values are set as requested.

Now, let's check the Before/After in the Survey site : missing Surveys are now available.

                        

That's all folks