Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-09-25
12
67 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 17

Expert Comment

by:Walter Curtis
ID: 41815368
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
ID: 41817071
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 17

Expert Comment

by:Walter Curtis
ID: 41817076
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:VW 63654
ID: 41839360
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 17

Expert Comment

by:Walter Curtis
ID: 41840109
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
ID: 41846069
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
 
LVL 17

Expert Comment

by:Walter Curtis
ID: 41846096
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
ID: 41851609
>>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 17

Accepted Solution

by:
Walter Curtis earned 500 total points
ID: 41852029
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
ID: 41856454
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
ID: 41856456
Refer my last comment.
0
 
LVL 17

Expert Comment

by:Walter Curtis
ID: 41856458
Yes, all goes through API and other systems as listed above.

Thanks and good luck...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Scenario: Let’s say you have a quote worksheet in Excel that you use to work up sales figures and such for your clients. You utilize SharePoint to manage and keep track of these documents. You would like values from your worksheet to populate Sh…
Microsoft SharePoint Foundation 2010 and Microsoft SharePoint Server 2010 do not offer the option to configure the location of the SharePoint diagnostic trace log files during installation.  This can, however, be configured through Central Administr…

839 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