tags:

views:

597

answers:

4

I have only been working with sharepoint for three months but right from the start I was told that the SharePoint content db was off limits as MS could change the schema at any time. The recommended route is to use the object model, and in most case I kind of understands that.

Now I need to join some lists in order to present the content grouped by some specific fields. Rather then iterating through each and every list I would prefer to link our own db which resides on the same DB server, to the WSS content DB and just create a view on the tables. This view should be on our DB in order to make such that we don't change ANYTHING on the WSS content DB.

Am I on the route to eternal damnation or not?

+4  A: 

Yes, you are. Microsoft is very clear that any modifications to the SharePoint tables renders you unsupportable.

Direct modification of the SharePoint database or its data is not recommended because it puts the environment in an unsupported state.

Now, creating a link on your own DB which queries the SharePoint DB is shaky ground. Personally I'd do one of two things:

  • If this is a mission-critical application, run it past MSFT support.
  • If it is anything else, just make sure that your view is not locking the DB during querying.

A better strategy might be to iterate the lists and sync it to your own table so you can do whatever kind of data-mining you'd like - if you don't mind whatever lag time your sync routine would need.

Cory Foy
OK, but doesn't that open a new can of worm like concurrency problems? If the update process is running at the same time as a user is updating one of the lists ?
Kasper
Don't see this is going to be a problem as the datacrawl that puts stuff into your own table will be getting from a database and inserting into a database. The database is pretty well designed to handle this.
Nat
Sure, but the data is then out of sync until the crawler runs again. Guess I should hook into the events on the lists in question in order to minimize that issue
Kasper
That's why I was saying if you don't mind the lag. If this is super-mission critical (by that I mean, important enough you all have Premier support contracts, yada yada) then I can point you at resources if you contact me at foyc at cornet design dot com. Otherwise OM and eventing is your best bet
Cory Foy
+1  A: 

SharePoint pretty much relies on total "ownership" of the underlying database. Small things like another process reading from the SharePoint database could potentially slow down SharePoint's operations in unexpected ways.

As SharePoint does not usually update in a "real time" manner, it should be good enough to create a process that queries the sharepoint lists and adds the data to a table in your own application.

Schedule the crawl for a low activity period and voila a solution that is not going to risk unexpected slow downs to SharePoint.

Start your search on querying SharePoint at SPQuery.

Nat
+1  A: 

Check out SLAM, SharePoint List Assocation Manager. It allows you to easily push your SharePoint data to SQL, including complex joins (one to one, one to many, many to many). And it keeps the data synchonized in real time.

http://slam.codeplex.com

Allan Wellenstein
Nice, thank for the link. That sure looks interesting
Kasper
A: 

Well, if the joins you need to do are pretty simple, defining a linked data source in SharePoint Designer may work for you

Sam Yates