Solved

How to create view table directly from sharepoint's database ?

Posted on 2016-09-25
12
45 Views
Last Modified: 2016-10-23
Hi,
I am very new to sharepoint. But I do have experience as dba and developer using Oracle database, Oracle Developer suite 10g, opensource etc.

My friend work as a developer, creating forms using sharepoint. All he did was to capture personal information, detail, education, higher qualification, etc. He told me that he never created any table. He just created forms and sharepoint somehow will create source lists just like table.

After few months, we need to extract all data but not via sharepoint. We want to access directly to the sql database because I believe this is more flexible (due to my background as dba for oracle). It give me freedom to create view table but not to update any.

When I check the database, I couldn't find specific table like what most developer do, create table and create form later. What I realised is, under SQL server dbo tree, I see Tables - further down AllDocs,AllDocStream,...AllLists, AllListAux etc...

Is this how sharepoint store the data as a list? Is there any tool that can read entire database and display it in table form ? Or is there any workaround ? Once I can run normal sql query statements, like , select * from person_tbl, education_tbl .....where ..condition (join statement), then I am happy. I can further develop myself.

Please note: My question is more on discussion how sharepoint works and how it can assist non-sharepoint developer to extract existing data. An example of this, what if I want to create mobile apps that need to extract captured data in sharepoint.

thanks.
0
Comment
Question by:VW 63654
  • 6
  • 6
12 Comments
 
LVL 14

Expert Comment

by:SneekCo
Comment Utility
This may be of interest to you;

https://support.microsoft.com/en-us/kb/841057

You should NEVER go directly to the SQL database to query or extract data in any way. That is gospel from Microsoft. From over 10 years experience I can tell you I have never had to for ordinary purposes as you describe. Of course I have broken that rule a few time for some very specific purposes, but always in a read only, no lock fashion. SharePoint provides everything you need to access the data that is within the SharePoint databases, be it a simple GUI such as what you find in Central Administration, a huge amount of PowerShell cmdlets to other tools, some being great free and for $$ 3rd party tools.

I have also know developers and dba's that don't listen to this and get very lucky and not muck things up too bad. They might be able to get some data to work with, but they don't know what they don't know. I usually have to clean up their messes. They reinvent the wheel and usually make something that is simple so complex that they even convince themselves how great they are.

I hope some other experts chime in here to add to this conversation. It's not about the points, it's about getting you some solid advice!

Good luck...
0
 

Author Comment

by:VW 63654
Comment Utility
SneekCo,
thanks for your reply,

>>You should NEVER go directly to the SQL database to query or extract data in any way.
seems like a directive statement not to use any 3rd party or whatsoever other than Sharepoint

>>SharePoint provides everything you need to access the data that is within the SharePoint databases, be it a simple GUI such as what you find in Central Administration...

For newbie like me, is there any MS Sharepoint for developer (evaluation version) to try with?
I like to try how simple it is to extract what had been captured in the forms.
0
 
LVL 14

Expert Comment

by:SneekCo
Comment Utility
Yes, there are evaluation versions for what you need to set up a test environment. Go to the TechNet web site and you will be able to download the software.

As far as data captured in the forms, keep  in mind, the forms are just interfaces to the data in the content databases.

Good luck...
0
 

Author Comment

by:VW 63654
Comment Utility
Hi,
Sorry for long delay responding to this. Actually I just downloaded it but have not install it yet due to other more urgent task. But in the meantime, I also expect from others to give other advice on how to tackle similar problem like mine.

thanks,
0
 
LVL 14

Expert Comment

by:SneekCo
Comment Utility
You may want to close this questions so it does not become abandoned and get deleted and start a similar questions when you are ready.
0
 

Author Comment

by:VW 63654
Comment Utility
SneekCo, I can't close it because the solution or advise so far is how to use sharepoint instead of answering direct question on how to create view table from sharepoint table. Unless there is no solution, ie, it is impossible to create view table from sharepoint database.

My other team still want to know how the sharepoint data can be used by  third party project, such as mobile apps etc. In fact this is one of the main objective of the question.

Using sharepoint also need time to get familiarised. But I will close or delete this question without solution if no one can help me out.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 14

Expert Comment

by:SneekCo
Comment Utility
I think you don't understand. You can do almost anything in IT, but that doesn't mean that you should do it. In this case, there is not an answer because it should not be done, therefore it can't be done. In your words "it is impossible to create view table from SharePoint database".

With your logic, if someone asks "How do I put a jet airline engine on a skateboard", you would wait forever for an answer because technically you could put that engine on a skateboard, but no one in their right mind would ever do it, nor try to explain how to do it.

I am fully aware what a SQL view is and what SharePoint is, and you should not combine the two. Period.

That is a professional answer. If you want some half ass jalopy answer, some no nothing developer might come up with some way to put that "jet engine on the skateboard", but when they start to use it, something bad will happen.

So if you want to know this;
My other team still want to know how the sharepoint data can be used by  third party project, such as mobile apps etc. In fact this is one of the main objective of the question.

Ask this.

Good luck...
0
 

Author Comment

by:VW 63654
Comment Utility
>>it is impossible to create view table from sharepoint database

Honestly I don't know how data are stored in sharepoint. I can only assumed data are stored  in sql server. I already explain my background on how I developed a system using oracle and third party tools.

May be I just close this.

Thanks anyway.
0
 
LVL 14

Accepted Solution

by:
SneekCo earned 500 total points
Comment Utility
Data is stored in MS SQL server of course, but it is in a form, the schema is set up so that it should not be touched or queried directly as you do with a traditional SQL database or with oracle.

There are several ways that you can work with the data however. All of that would go through the SharePoint API and interfaces, not directly to the SQL database.
Browser
Web Services
Programmatically with various languages
Powershell
vba / vbs
Infopath
Excel / Access
SSRS Report Builder
and probably a lot more...

So yes, you can use the data that SharePoint has stored in SQL server, but don't use SQL server management studio and go to "create view" and start to do queries and build views or do any other process directly on the database. (Of course management tasks such as backups are done at the database level.)

Good luck...
0
 

Author Comment

by:VW 63654
Comment Utility
SneekCo,

Thanks. That is more better than your analogy "skateboard". Now I can understand from sharepoint perpective vs traditional development.

>>but don't use SQL server management studio
Anything can be done but via API, period.

It's gonna be challenging for me because many system had been developed by many other developer. I have no worry on new system, maybe we just start sharepoint. But as for existing one, we have to study system dependencies as well.

As for now I will accept your last comment as the best solution, not directly answering the question but giving me ideas and the way forward understanding current and future issues in system development.

I presumed integration with mob apps also be via the API.

Thanks for your help.
0
 

Author Closing Comment

by:VW 63654
Comment Utility
Refer my last comment.
0
 
LVL 14

Expert Comment

by:SneekCo
Comment Utility
Yes, all goes through API and other systems as listed above.

Thanks and good luck...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A question that is asked often, is how to generate sequential numbers in InfoPath Forms. The best way to achieve this is to use a SQL database, along with a stored procedure and a web service to connect Forms Services to the DB. The first thing t…
I recently came across an issue with a MOSS 2007 deployment where access into some sub-sites were denied, even for the MOSS farm administrators. A bit of background to the setup of this MOSS farm; this was a three server setup, consisting of a fr…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now