Solved

Sharepoint foundation 2013 and SQL

Posted on 2014-09-18
6
593 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:cynthia4601
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
MSSQL 2014 Query Synthax 8 38
c# code 19 57
Help with SQL Query 23 39
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

18 Experts available now in Live!

Get 1:1 Help Now