Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am attempting to create a Database View derived from Sharepoint List Data in Sharepoint 2010.

Currently to do this I have simply added a new List Item with Unique values, and manually mapped the columns to their corresponding database columns.

For example: FirstName maps to nvarchar20, LastName maps to nvarchar21 ect...

After doing this a couple times I figured there has to be an easier way of determining this mapping without having to go through each column manually to determine where the data is stored in the database.

Is there a simple way of mapping List Columns to Database columns?

share|improve this question
add comment

4 Answers

up vote 4 down vote accepted

I'd highly discourage doing this kind of operation.

Even though reads from the content database only makes your installation unsupported as long as they are occuring, you shouldn't do them.

The problems you'll run into are:

  • You are very likely to affect SharePoints operations in unsupported ways remember you can't even read without introducing locks
  • You run a very high risk of returning wrong information as you may not fully understand the database tables, including how versioning, approval, recyclebin, ... is handled
  • Even if you don't run into these problems initialy any service pack/hot fix installed on SharePoint may change database schema/access in such a way that you run into them

But if AGAINST MY ADVICE you still want to do it. Then you can get information about each fields placement in the database by enumerating the lists Fields and from the SPField.SchemaXml retrieve the attributes ColName and RowOrdinal

Please look into if providing a Web/REST service isn't an option.

share|improve this answer
add comment

What are you attempting to accomplish? Reading SharePoint databases can cause locking problems. Writing to SharePoint databases will put you in an unsupported state. See KB841057

share|improve this answer
    
I am simply creating a View to integrate with another system. I have only supplied "Select" permissions as I am aware of the locking problems that become present when you begin altering data. –  Lando Oct 16 '12 at 18:59
    
Why are you unable to use the SharePoint OM? –  Trevor Seward Oct 16 '12 at 19:01
    
I am not using the OM because we are integrating with a Proprietary system and there are some binding Politics. We do not want them seeing more data than they need and I am also not in control of how they access our data. We decided that providing a View was the most simple and Secure way to provide the data. –  Lando Oct 16 '12 at 19:09
    
No matter how you slice it, Microsoft will not support an environment where you are querying the SharePoint databases directly. You will need to use one of the other methods Microsoft does support (i.e. using the object model, using the web services, etc.). –  John Chapman Oct 16 '12 at 19:14
add comment

Microsoft does not support accessing SharePoint database directly via SQL. As @Trevor said, it will put your environment in an unsupported state and we (in this community) cannot provide you any guidance that would result in such.

Sometime ago I did write a blog post about how to access SharePoint data via the SharePoint object model in an SQL CLR function. You could give this a shot: http://www.sharepointjohn.com/sharepoint-2010-sql-server-2008-query-the-sharepoint-object-model-from-a-net-sql-server-clr-function/

share|improve this answer
add comment

Another option would be to hook into the list via SQL Server Reporting Services and create a reporting endpoint. This would then allow you to secure the data as you want, fetch the data in a supported way and provide the users the functionality they need.

Access to the report is secured through SSRS and then within the report itself, you can dictate who is going to see what.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.