Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a table in SQL server 2008 and a list in sharepoint 2010 that has a few columns from this table and a few other unrelated columns as well. This SQL server table keeps changing and I want to give a sharepoint admin an option to sync this list with the table in SQL server whenever he wants to. Question: What is the best way to connect to SQL server in this situation? I read up on the following options: - Using external content types,Business connectivity services - Creating ASP.NEt application page - Creating a visual web part

Which of these options is best for this problem.Are there other good methods that I haven't mentioned?Please point me in the right direction.Thanks!

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Based on your comments of

list in sharepoint 2010 that has a few columns from this table and a few other unrelated columns as well

and

sync this list with the table in SQL server whenever he wants to

using an External Content Type (ECT) with the SQL Server table as the data source, and then adding those fields as External Columns to your list is the best approach.

Connecting to the ECT using External Columns will allow you to retain your "few other unrelated columns" in the list. Also, the information doesn't automatically update (except when adding or editing an item) for the whole list. So the SharePoint admin can choose when to do bulk refreshes (with the little refresh icon that shows up in the external column) when he desires.

If you just created an External List from the ECT, you would not have the other unrelated columns, and the data in the list would always refresh to show what is in the database table.

You also asked

are there other good methods that I haven't mentioned?

You could create a SQL Server Integration Services (SSIS) package to update the SharePoint List from the SQL Server database and schedule that to run with SQL Server Agent or the Windows Scheduler (or have it run as-needed by your SharePoint Admin).

You also could write a Powershell script that would do the same thing, i.e., read the values in from the database and update the items in the list. The script also could be scheduled or run manually.

share|improve this answer
    
Thanks for the reply.I'm going to try out ECT and see how it goes from there. –  user1630812 Jun 10 '13 at 2:53

Take a look at a third party tool called BDLC too. I used it on a project recently and it was pretty easy to use.

share|improve this answer

The options you are mentioning are all valid. If you are looking for the easier solution I would say external content type.

There are plenty of blogs an videos about it here

Web parts and application pages, will require more effort and a developer (in case you not :))

So if the requirement is just show an up to date version of the row in the table I'd go for external content type.

Hope this helps

share|improve this answer
    
I'm going the way of ECTs. Thanks for the reply. –  user1630812 Jun 10 '13 at 2:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.