Just a quick note to let you know that part 2 of “Creating mailing lists for SharePoint sites and blogs” series has been published yesterday on EndUserSharePoint .
I would like to thank everybody who sent me their feedback, I’ll be waiting for more 🙂
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
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:
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:
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!
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 :
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 :
Workaround :
2.Use the following expression :
Results :
1. Each integer appears only once on the value axis.
Hope this helps:)
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!
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 Choose a “contacts list” template to start with and specify a name for the list.
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.
6 .Switch to the security tab
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.
9. You will be prompted that you are breaking the inheritance from the parent site, click Ok
10. The Setting menu has now appeared. Open it, and choose ‘Anonymous Access’.
11. Configure the anonymous users’ permissions as shown in the following figure then press Ok.
12. Navigate to the list settings page using the breadcrumb.
13. Choose “Advanced Settings” from the General settings tab.
14 . Configure the advanced settings as shown in the figure below.
15 . Navigate back to the list, click new contact.
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
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.
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.
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..
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.
Last week, I worked on creating a mailing list for a public facing SharePoint site. I really had some constraints because I was only allowed to use SharePoint Designer and the browser. I’m not used to these situations because I am mainly a software engineer. However, it was a very nice experience. I applied lots of knowledge and I worked around the constraints. I decided to put the experience and workarounds together into an educational series of articles to help SharePoint end users and administrators create their own mailing list without writing a single line of .NET code.
Here is a snapshot of what I ended up with last week:
In the next part, we will create a SharePoint list based on the Contacts template provided by SharePoint. We will configure the list to allow anonymous contribution but not anonymous access. In other words, anonymous users should be able to add items but not navigate to the list data. Stay Tuned
A couple of days ago, I had troubles with anonymous access to a custom form for a custom list. I created a list, and added a DataFormWebPart to the page using SharePoint Designer as shown below.
I set the page and the list to allow anonymous access, and I could get to both. However, when I opened the page with the custom list form, the DataFormWebPart displayed the following error :
Resolution :
Just set DataSourceMode of SPDataSource control to Webs or ListOfLists as shown below :
For more information about the differences between Webs and ListOfLists, See this SharePoint Designer Team Blog post for a decent explanation of these options:
Sometimes you will encounter something very weird in the ULS logs. The only entries that are logged are “Tracing service lost trace events” log entries. To resolve this issue, just restart your Tracing service. You can do that from the command line using the following commands:
net stop sptrace (to stop the tracing service)
net start sptrace (to start the tracing service)
This will do the trick for you.
In addition, you may want to check out the free SharePoint Troubleshooting issue I recently authored forUnderstanding SharePoint Journal .
Ayman El-Hattab's Technology Blog 