Solved

Sharepoint foundation 2013 and SQL

Posted on 2014-09-18
6
636 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
[X]
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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

626 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