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

Sharepoint foundation 2013 and SQL

Posted on 2014-09-18
6
609 Views
Last Modified: 2014-09-23
I have a custom input form that updates a sharepoint work order list.  Ultimately, I would like to get the Work Order list data into SQL.

I created an external work order list that updates a SQL work order table -- it works.  The external work order list  and the sharepoint list have almost all of the same fields set up.

1) Is there a way to have the work order list update the external list (and the external list would automatically update the SQL table)?
2) Should I change the custom input form so that the form updates 2 lists at the same time - the sharepoint work order list and the external work order list (and the external list would automatically update the SQL table)?
3) There is a better solution ? :-)
0
Comment
Question by:cynthia4601
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:colly92002
ID: 40335450
I thnk you should use the Business Data Connectivity Service.
http://technet.microsoft.com/en-us/library/ee661740%28v=office.15%29.aspx

With this, you can define a single dataset and expose it as a SharePoint list.  You can then use all the features of SharePoint to manage that list (security, forms etc), with full CRUD operations being performed by the SharePoint front end.  You get to use all the nice client features of SharePoint but keep your data externally in SQL.
0
 

Author Comment

by:cynthia4601
ID: 40338256
Thanks for the response.  Yes - I have the external list working, which uses BCS.  My problem is that I want a SharePoint list to update the external list (the external list is already using BCS) and don't know how to do that.  I thought it might be more through an SSIS?  If I don't use the sharepoint list to update the external list, then I will have to rewrite the work order app to update the external list.  This will be a huge headache.

Thank you!
0
 
LVL 15

Expert Comment

by:colly92002
ID: 40338542
BCS offers full CRUD operations.  You can configure it using Sharepoint designer as described here:
http://lightningtools.com/bcs/creating-an-external-content-type-with-sharepoint-designer-2013/

Here is a full overview of BCS for SharePoint 2013 (it is very powerful)
http://msdn.microsoft.com/en-us/library/office/jj163782%28v=office.15%29.aspx
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:cynthia4601
ID: 40339345
Thank you again -- however, I already have set up BCS and understand how powerful it is.  I need to know how to have an existing sharepoint list update the external list.
0
 
LVL 15

Accepted Solution

by:
colly92002 earned 500 total points
ID: 40339882
If for some reason you want to have two non-synced data sources (a Sharepoint list and and separate SQL database table that is not linked via BCS) and you want to keep them "synced" then you are going to have to write some event receivers to work on create/delete/update in c#/Vb and create a sharepoint feature to deploy them.  However why would you when BCS does this all for you?  Also there is no way of doing this from the SQL direction.

BCS allows you to expose an external data source to SharePoint and use SharePoint to manage it as though it were an internal SharePoint list.  This includes writing back changes, deleting rows, creating rows etc.  You can edit the list in datasheet mode, or edit each line in the Sharpoint list form, or define your own form in SharePoint designer, its up to you.  As far as SharePoint and it's users are concered it is a SharePoint list, but you hold the data in an external SQL database, meaning you can do whatever else you want to do with it.  

IF you already have a list exposed through BCS, just set up the C, U, and D (Change, Update and Delete)  operations in Sharepoint designer as described in the first link.  You will already have the S (select) operation defined.

It is possible to extract SharePoint lists to SQL  using SSIS but this will be a job that is run at intervals so will not be perfectly upto date.  I do this as an overnight run to extract reporting data, but this does not seem to be what you want?  Furthermore, if you already have BCS already configured and selecting the data for the Sharepoint list  then it is MUCH simpler (and frankly just better) to use this than SSIS.
0
 

Author Closing Comment

by:cynthia4601
ID: 40340267
Not the solution I really wanted but I guess it is the best solution.  I didn't want to have to re-write the application to update the external table but sounds like I should (and avoid the SSIS).  Thank you for your help.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

838 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