Five reasons not to directly query SharePoint databases

I am writing this blog post because I have recently come across numerous blog posts and articles discussing how you can directly query SharePoint databases. I have also stumbled upon a web part available for free that shows the most popular content among the SharePoint farm, this web part directly queries the SharedServiceProvider database to retrieve the information needed by the web part. Moreover, I have recently downloaded a tool that grabs deleted documents from the content database and saves them back to your hard drive.

Before diving into the pros and cons of querying SharePoint databases, let’s explore them and see how we can write direct T-SQL queries against them.

Below is a very high-level database diagram. I would like to extend a special Thank you to Ethan for coming up with this useful diagram.

DISCLAIMER : I highly recommend executing the following queries in a non-production environment.

 

For instance, dbo.AllUserData is a table that holds information about all the list items for each list. Here is a sample query to retrieve the contributions of each user in a certain site collection:

 

SELECT      tp_author, count(*) as 'Number Of Documents & Items'
FROM        dbo.AllUserData
Where       tp_SiteId='GUID'
Group by    tp_author

 

And here is another query to the SharedservicesProvider database that retrieves the most popular documents:

 

SELECT ANLResource.DocName, COUNT_BIG(*) AS HitCount 
FROM ANLResourceHits 
INNER JOIN 
ANLWeb 
ON ANLResourceHits.WebGuid = ANLWeb.WebGuid 
INNER JOIN 
ANLResource
ON ANLResourceHits.ResourceId = ANLResource.ResourceId 
WHERE (CHARINDEX(’.aspx’, ANLResource.DocName) = 0) 
AND (CHARINDEX(’.swf’, ANLResource.DocName) = 0) 
GROUP BY ANLResource.DocName 
ORDER BY HitCount DESC

 

Well, I know it’s incredibly easy, you can extract as much data as you want and consume this Data in your custom solutions (Reports, Web Parts,…) and the sky is the limit.

BUT

  1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
  2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
  3. Directly querying the database can place extra load on a server and hence performance issues.
  4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
  5. Your custom queries might lead to incorrect data being retrieved.

A Debate about the stability issues

I have recently took part in a debate on one of the forums with a guy who claims that there are no stability issues when you directly query the database since you can use the With (NoLock) clause in your TSQL Queries as follows :

Select * From dbo.AllDocs With (NoLock)

 

This may seems better, you can avoid the deadlocks by using (NoLock) clauses BUT you will run into another problem which is retrieving incorrect data because you will be observing the database in an intermediate state (Data are not committed yet).

Insertions and modifications of the SharePoint databases

It is clearly unsupported to update, delete, or insert records. The risks are surly far more obvious.
Also be aware that any database changes would definitely break the supportability as stated by Microsoft. Examples of such database changes include, but are not limited to, the following:

  • Adding new indexes or changing existing indexes within tables .
  • Adding database triggers.
  • Adding, changing, or deleting any primary or foreign key relationships.
  • Changing or deleting existing stored procedures.
  • Adding new stored procedures.
  • Adding, changing, or deleting any data in any table of any of the databases.
  • Adding, changing, or deleting any columns in any table of any of the databases.
  • Making any modification to the database schema .
  • Adding tables to any of the databases.
  • Changing the database collation

The proper and supported ways for data access in SharePoint

The process of data-retrieval in SharePoint should be executed via the SharePoint object model or the built-in SharePoint web services and this is attributed to the following:

  1. Microsoft has gone through a lot of work to ensure that using the object model or web services will lead to stable and efficient database interactions.
  2. The likelihood of Microsoft breaking their own object model is far less that the database schema changes.
  3. You are not going to lose Microsoft supportability.

Summary

 

 

Direct Queries to SharePoint databases is a considered a no-no, you should use SharePoint object model or web services instead.Do not worry, I will show you in later posts how to do the data access through the object model, stay tuned!

SSRS Issue : Integers repeat on value axis!

Yes, this is a non-SharePoint post, but it’s about a bug in SSRS that really irritated me. In the project that I’m currently working on, I was asked to create some reports that measure the number of contributions of portal users in SharePoint lists and libraries.

I’ have chosen to use SSRS 2008 and since I’m counting the number of contributions, I have set the format for the captions on the value axis to Number with 0 decimal places since decimals do not make sense in my situation. The chart is parameterized to the values on the value axis vary from small numbers to large numbers according to the passed-in parameter value.

Issue :

57

As shown above, the integers on the value axis get repeated since SSRS decides that the axis should display 0, 0.5, 1, 1.5, 2, 2.5, 3,3.5,4. And since I have set the decimal places to zero, this ends up being displayed as 0, 1 , 1 , 1 , 2 , 2 , 3 , 3 , 4 , 4 .

Steps to reproduce :

  1. Create a bar chart in SSRS.
  2. Make sure that the passed-in parameter value leads to small values where the maximum is 4 as shown above.
  3. Set the value axis number format to Number with zero decimal places as shown below.
  4. View the chart using your browser or in Visual Studio.

58

 

Workaround :

  1. Set the interval to be dynamic from the value axis properties.

59

2.Use the following expression :

60

 

Results :  

1. Each integer appears only once on the value axis.

61

Hope this helps:)

Custom Form – Edit Fields Based on Permission Levels in SharePoint (Screencast)

This time, I’m not the presenter. This time it’s Laura Rogers, the highly active SharePoint expert and presenter. This webcast shows how to create conditional formatting in a custom list form, in order to hide or display fields based on the logged in user’s permission. I really liked the trick, that’s why I decided to share it from my blog!

Also, this is the link to Ian’s blog that Laura referenced in her screencast.

Ah, by the way Laura also authors very beneficial articles for EndUserSharePoint.com where I author mymailing list series, check out her articles here!

Creating mailing lists for SharePoint sites and blogs [No Code Required] – Part 1: Creating a secured backend

NOTE : This article was published on EndUserSharePoint.com a couple of weeks ago and because of the wonderful feedback I received, I decided to cross-post it here too.

This multi-part series of posts is intended to help you create mailing lists for your SharePoint sites or blogs without writing a single line of .NET Code. In the introductory article, I listed the project initiatives, requirements, and objectives. If you have not read it yet, I would encourage you to do that first.

As per the requirements listed in the introductory part, we need to create a back-end where we will store the subscribers’ contacts and information. Since everything in SharePoint is stored in lists or in libraries, we will use a Contacts List to hold our users’ information. We need anonymous users to be able to contribute to this list by adding their contacts without giving them any sort of access to the list views. In addition, we need to set in motion the content approval feature provided by SharePoint to stop subscribers from receiving e-mails unless they are permitted. That being said, let us get started…

For this series of articles, I’ve assumed that you already have a SharePoint site set up and that it is accessible to anonymous users. If you do not know how to configure your SharePoint site for anonymous access, check out this short video.

  1. Open up SharePoint Designer, connect to your SharePoint site collection, and supply your credentials.
  2. Choose a location from the folder list as to where you want to create the list that will hold the subscribers’ contacts.

 

45

 1 Choose a “contacts list” template to start with and specify a name for the list.

46

4 . Go to the newly created list from SharePoint Designer, right click, and select properties.

5.  The list properties dialog box opens up, open the settings tab, uncheck “Enable attachments” and check “Require content approval”. Press the “apply” button. Remember that we need the emails to be sent only to the approved subscribers.

47

6 .Switch to the security tab

48

7. Click “Manage Permissions using the browser”.

8. The list by default inherits permissions from the site, but for anonymous lists, this will not do. By selecting ‘Edit Permissions’ from the ‘Actions’ menu, you can specify specific permissions for this list.

49

9. You will be prompted that you are breaking the inheritance from the parent site, click Ok

50

10. The Setting menu has now appeared. Open it, and choose ‘Anonymous Access’.

51

11. Configure the anonymous users’ permissions as shown in the following figure then press Ok.

5212. Navigate to the list settings page using the breadcrumb.

13. Choose “Advanced Settings” from the General settings tab.

54

14 . Configure the advanced settings as shown in the figure below.

55

15 . Navigate back to the list, click new contact.

56

16. Copy the URL from your browser address bar to Notepad. Edit the value of the “Source” query string parameter to be the home page of your site. In my case, I ended up with the following URL

http://www.sitename.com/Lists/EUSP%20Mailing%20List/NewForm.aspx?RootFolder=%2FLists%2FEUSP%20Mailing%20List&ContentTypeId=0×01060031DDECE1AF719B4DA173919310A8F264&Source=http%3A%2F%2Fwww%2Esitename%2Ecom

 Note : You must edit “Source” value, otherwise your visitors will be redirected to the default view of the list after submitting the request and they will be prompted to provide their credentials.

17 . Now everything is set up for your mailing list form, you just need to provide your visitors with a link to subscribe to the list. You can add a content editor web part to your home page and insert a link to the URL that you ended up with in the previous step.

18. Access the site anonymously and navigate to the Mailing list form through the content editor web part you created in the previous step.

19. Fill out the form and here you go! You are redirected back to the home page.

Note: Anonymous users will not be able to navigate to the list views even through the breadcrumb. They will be prompted to enter their credentials or they will be redirected to the Access Denied page.

Summary:

In this post, I’ve illustrated creating SharePoint lists through SharePoint designer, configuring them for anonymous contribution, and preventing anonymous users from accessing any submitted data. I have also demonstrated configuring SharePoint lists to enable content approval.

In the next post, we will create a custom SharePoint list form through SharePoint designer like the one shown in the introductory part, manage the “Contact” content type, create client-sided validations and much more …

About EndUserSharePoint:

EndUserSharePoint.com is a community of SharePoint authors dedicated to providing content to SharePoint End Users on three levels: Information Worker, Power User/Site Administrator and Site Collection Administrator.

ItemPublished event !!!

I know the title seems weird, but that was exactly the subject of an e-mail message that I have received today from a SharePoint developer asking about the way he can execute some code when a document is published. The guy needs to send out the URL of a document as soon as it is published to a SharePoint group members for taking some actions.

Actually there is no such thing as “ItemPublished” event, the subject of the e-mail was misleading, the guy was searching for such an event but unfortunately SharePoint doesn’t provide it in its object model.

Workaround :

When a document in a library is published, “ItemUpdated” event fires and the only way to detect that an end user has chosen to publish a document is  by checking ModerationInformation in ItemUpdatedevent handler to see if the list item is in a published version or not. Approved is published.

properties.ListItem.ModerationInformation==SPModerationStatusType.Approved

 

Hope this helps..