Friday, July 11, 2008

Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-2

Hi All,

Here we are with Run SQL Queries and Fetch List Data from SharePoint Database (WSS_Content) Part-2
For those who are want a quick refrence of how to query Sharepoint content database
View Part-1 :





Problem Statement:

I have a List “Company Information”
I have a List “Contact”The relation between them is
“Company Information” List Item ID is Foreign Key in “Contacts” List.
I want to Show a web Part with data like:
Contact – x Belongs to Company – x
Relate the Problem and the Display of result with the above screen shots

Solution with SharePoint Object Model:

Fetch Each List Item from List “Company Information”
Get the Company ID
Fetch List Item from List “Contacts” where “Company ID” is present.
Append this information in a Data Grid and Display.
In short you will have to Loop throught the List,Compare and Display.

Solution with SQL Query on SharePoint internal Content Data base:
There are Two Lists in SharePoint internal Content Data Base
1) Lists 2) UserData

We have explained about this in Part -1.
For More information refer to this Post.
http://www.sharepointkings.com/2008/07/run-sql-queries-and-fetch-list-data.html

Write a SQL Select Query.
Run it and test it in as a New Quey in SQL Query Pan.
Take a Data Grid and Bind it with the SQL command.
Simple isn’t it !!!!

Core Logic:

Now how do you get it?
The TABLE: UserData stores all the Data (Contents of any List).
When you will query the Wss_Content data base
With a query like
Select * from UserData;

You will initially go mad. But don’t freak out. Try to understand the relation ship.
When you want to get the data from multiple Lists,
You will have to apply a Self Join to the TABLE : UserData.

To understand more do a practical.
Create Two Lists as you see in the screen short
Copy the following queires and Run it on your Sharepoint wss_content database.
In this case the Sharepoint wss_content database is : WSS_Content_11112
use WSS_Content_11112

select nvarchar1,nvarchar3,nvarchar4 from UserData where tp_ListID in (
select tp_ID from Lists where tp_Title = 'Company Information');


select int1 as 'Company ID',nvarchar1,nvarchar3 from UserData where tp_ListID in (
select tp_ID from Lists where tp_Title = 'Contacts');

select CI.nvarchar1 as 'Company',C.nvarchar1 as 'First Name',C.nvarchar3 as 'Last Name'
from UserData CI, UserData C
where CI.tp_ListID in (
select tp_ID from Lists where tp_Title = 'Company Information')
and C.tp_ListID in (
select tp_ID from Lists where tp_Title = 'Contacts')
and CI.tp_id = C.int1


Refer below SQL Query Screep short:



Disclaimer
Running Direct queries on Sharepoint Database is not recommended by Microsoft.
Simply it's not for the faint of heart. Directly querying or modifying the database can place extra load on a server, or can expose information to users in a way that violates security policies or personal information management policies.
I highly recommend doing all testing in a non-production environment.

9 comments:

Joel said...

Very nice article, i thought that is not possible to do. I am looking for a coming post about this area. Thanks.

Fran├žois said...

Great, but is there a list somewhere of the Sharepoint SQL columns equivalent ?

Ayman M El-Hattab said...

Five reasons not to directly query SharePoint databases
http://www.sharepoint4arabs.com/AymanElHattab/Lists/Posts/Post.aspx?ID=99

SharePoint Kings said...

Francois,
working on it.

SharePoint Kings said...

Ayman,
great post

Ayman M El-Hattab said...

Thanks man :)
By the way I really like your blog, keep up the good work:)

Anonymous said...

Nice! Thank you so much, I really needed this!

Anonymous said...

Great article. Exactly what I was looking for :-)

Ulf said...

Hi,

I would really encourage everyone that are working directly against the MOSS/WSS database to have a look at Camelot .NET Connector from bendsoft. It can be used to query lists and other sp content using standard sql syntax and is free for developers to use. Have a look at the documentation on our website (bendsoft.com) and see what your think about it.

Ex:
select * from `shared documents`




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com