1

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?

Improve this question

4 Answers 4

Reset to default
4

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.

Improve this answer
2

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

Improve this answer
4
  • 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. Commented Oct 16, 2012 at 18:59
  • Why are you unable to use the SharePoint OM? Commented Oct 16, 2012 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. Commented Oct 16, 2012 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.). Commented Oct 16, 2012 at 19:14
1

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/

Improve this answer
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.