Solved

Sharepoint foundation 2013 and SQL

Posted on 2014-09-18
6
625 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

734 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