SharePoint Surveys – Finding the Results in the Database

A while ago, I worked on a project with one of my developers that required building a web part to display the results from a SharePoint survey in a different manner.  The survey itself was a self-assessment… it was composed of 13 questions with approximately five options under each question, and the answers were the ‘Rating Scale’ type.  Below is an example of the survey:

While SharePoint does provide basic reporting out of the box, it never turns out to be what the client needs.  Because of this, a custom web part was built that allowed an administrator to look at the survey results in a variety of ways – by User – with a drill down to the individual Questions, or Summarized – with a drill down to individual questions & users.  An example of the summarized results view by question appears below:

So now I’ve shown off the cool stuff.  Before I go any further, I would like to thank Josh Carlisle for his assistance on this – without it, this post wouldn’t exist.  Now – the reason I’m really writing this post is because I’ve seen questions asked in the past, and I figured I could lend a bit of a hand with Josh’s guidance… the questions were usually…

“How do I find the survey results in the SharePoint database?”

“Where in SQL are the SharePoint survey answers stored?”

And my first reaction is one of caution… from the mouth of Microsoft…

Warning  Modifying the database schema or database structures is not supported. Changes that you make to the database contents may be overwritten when you install updates or service packs for Windows SharePoint Services, or when you upgrade an installation to the next product version.

Okay you say – you understand… and you want to go forward with finding the results data… well, let’s do it!  I’m running SPS 2003 on SQL 2005, so my screen shots are from the SQL 2005 Management Studio – but you can do everything in the SQL Clients Tools in 2000 as well.

1. First, you need to crack open the Content Database of the portal which contains the survey and open up the Lists table.

   

2. I would recommend you expand the width of the tp_ID & tp_Title columns to make your life a bit easier.
3. Now,  scroll through the tp_Title columns until you find the name of the survey you are looking for.  In my example, I’m looking for the ‘Horizons Self Assessment’.

   

4. Once you’ve found the survey name in the tp_Title column, look at the tp_ID – copy & paste or jot down the ID of the survey.
5. Now, open up the UserData table and expand the width of the tp_ListID column.

   

6. You’ve got some digging to do now.  You now need to scroll through this table and find the ID that you wrote down before in the tp_ListID column.  Note:  If the survey has been completed multiple times, then the ID will show up multiple times in the tp_ListId column (Each survey submission represents one record in the table).  In my example, I’m looking for the ID of ‘595665fc-6c09-424a-964d-d137b3db5a30’.
   1. I don’t have SQL 2000 Client Tools in front of me (although I know this can be done), but in the SQL 2005 Management Studio, I don’t have to dig to find the ID.  I can just right click on the column, choose Pane -> Criteria.  Then, in the second row up top under Column, select the tp_ListID.  In the Filter column for that row, copy the ID in.  Then execute this query – and you’ve got all of your survey results right in front of you.  The following image shows an example of the query & results:

      

   2. Or, feel free to use the following query:
      SELECT *
      FROM UserData
      WHERE tp_ListId = ‘unique identifier here’
7. So you’ve found the rows that contain your survey results – should be no problem pulling out the results now, right?  Well, not exactly.  Depending on the types of questions you are asking in your survey, that dictates how ‘cleanly’ SharePoint stores the results.  So the easiest way to start out is to scroll through the columns to your right, and you will see the survey results stored for that particular response.  In my example, using all Rating Scale questions, all of the survey results are stored in the ntextX columns, where X is equal to a number.  This is a real pain, as Rating Scale questions each get their own ntextX column, but then each Option & answer pair for that question is dumped into the single column that then needs to be parsed.  So, let’s take an example:
   1. My survey has one Rating Scale question about ‘Time Management’, and there are 6 options that the user can rate under this question.

      

   2. Now, once this is stored in the database, it appears for me in the following format in the ntextX column:

      Establishes satifactory work-life balance.;#+4#Prioritises workload and conflicting demands to achieve key results.;#+4#Organises self and others to meet deadlines.;#+3#Delegates work to achieve optimum overall performance.;#+3#Challenges and improves upon processes which waste time.;#+4#Reduces the impact of distractions upon self and others.;#+3#

   3. So now, in order to use this data, I would have to parse it.  But obviously, the data is there and available for you if you need to work with it.

Finally, just a quick reference guide for you.  I’ve done some research, and I’ve found that the different types of survey answers are stored in different columns… this is a summary of my findings.

TYPE OF ANSWER	COLUMN NAME
Single line of text	nvarchar
Multiple lines of text	nvarchar
Choice	nvarchar
Rating Scale	ntext
Number	float
Currency	float
Date & Time	datetime
Lookup	int
Yes / No	bit

And if you are interested in the database schema, you can always check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/tsdbTables_SV01051532.asp

New B&R Business Solutions Web Site

I am happy to announce that after a long delay, we have launched the new B&R Business Solutions web site today.  You can check out the site at www.bandrsolutions.com.

Enjoy,
Chris

Rights Management Services: Remote Access Quick Notes

Last week, before I took some time off, I setup Windows Rights Management Services (RMS) with the Information Rights Management (IRM) Client on my local PC for B&R. Since my laptop does not belong to the B&R domain, I couldn’t just click on the Permissions button in Word/Excel/PowerPoint/Outlook… if I did, I would just be presented with the option to use my Passport account… but I had to be able to create & consume rights-protected B&R documents on my laptop when traveling.  So in a nutshell, here is a very brief rundown on how I got this functioning:

1. Before setting up RMS, setup the IIS web site that will be utilized for clients connecting to RMS.  Make sure of the following:

   1. Assign the site a static IP address.

   2. If needed, add in the host header (if using the IP for more than one site)

   3. Directory Security – Integrated Windows Authentication

   4. Install your SSL Certificate before configuring RMS.

   5. Setup DNS within your environment so that internal users are forwarded to the site (ex. rms.domain.com) – add an A record to point rms to the IP address of the site.

   6. Setup your external DNS now as well.

   7. Open up port 443 on your firewall and forward it to the IP address.

   8. TEST NOW – make sure (After DNS has propagated) that externally you can hit the dns name of the site (rms.company.com) and you are prompted to input login credentials. Also make sure this works internally.

2. Provision RMS on the web site you setup. Walk through the setup and keep in mind:

   1. When specifying the Extranet Cluster URL – specify HTTPS & the Fully-Qualified Domain Name… DO NOT use the server name here. Otherwise you can run into serious problems migrating in the future.

   2. Use a domain account as the service account – not a local system account.

3. Register your service connection point after provisioning is successful.  Note that the SCP should be similar to https://rms.company.com/_wmcs/Certification/Certification.asmx.

4. You’re then basically setup. If you have a system on the domain, open up an Office Application & attempt to utilize RMS.  It should find the server and allow you to encrypt the document.

5. For any non-domain / external users.  Before they use RMS for the first time, two additions need to be made to their registry settings (make sure the RMS client is installed first, then make these registry additions):

   1. HKLM\Software\Microsoft\Office\11.0\Common\DRM\
      Create a new String Value called CorpCertificationServer and make the value point to: https://rms.company.com/_wmcs/Certification.
       
   2. HKLM\Software\Microsoft\Office\11.0\Common\DRM\
      Create a new String Value called CorpLicenseServer and make the value point to: https://rms.companyname.com/_wmcs/Licensing.
      
6. Then open up an Office Application and click on the RMS button.  You should then be presented with login credentials.  Enter the domain\username & password, and you should be rolling.  Note: if you receive any error messages after clicking on the RMS button, check that you specified the correct URL.  Also – if you are installing RMS & utilizing SQL Server 2005 – you must perform the fix in this KB Article, otherwise it will error out.
7. If you have any questions / issues / errors, let me know!

One other quick thing to note… when you are on the ‘Windows RMS Administration’ page and you click on ‘Administer RMS on this web site’ – it probably won’t load – and if you notice the URL, it will be http://localhost/_wmcs/Admin/default.aspx or something like that.  Note that all you need to do is replace localhost with the DNS name you gave the site (rms.companyname.com or whatever), then you can access the admin page.

Enjoy RMS and the great security it offers!