3

This is a tossup between whether it belongs on Stack Overflow or Server Fault (but since I am not a developer, and am looking for a no-code solution, I'm starting with SF).

Is there an easy way to populate a SharePoint list with data from an external SQL database? In SharePoint designer, it is a simple matter to create a connection to a SQL database for using the data view web part, but I need the data from a SQL table to populate a SharePoint list. Is there a no-code way to do so?

Edit: Although there are certainly ways of exporting data from the SQL table and importing it into my list, I am looking to have the connection be live so that a column in my SharePoint list is always up-to-date with a master data source stored in a different SQL database.

Improve this question

4 Answers 4

Reset to default
5

Another option that you can look into is the Business Data Catalog. While this is more complicated it does allow you to have a SharePoint view of the data. The downside to this is that it does require the Enterprise edition and pretty advanced skills to get the BDC configured to point to your custom database.

Since you said you are not a developer, I would tend to lead away from the BDC unless you are able to hire someone to configure this for you.

Improve this answer
2
  • Thanks Aaron- I think that is my only real choice. It looks like there are some good tools to generate the BDC application definition file, namely Lightning Tool's BDC Meta Man (lightningtools.com/bdc-meta-man/default.aspx) or Simego's BDC Design Studio (simego.com/products/MOSS-BDC-Design-Studio). I think I'll start with the MOSS SDK (the price is right) which comes with the Microsoft Business Data Catalog Definition Editor and some sample BDC connection files. We'll see if it's simple enough for an admin to figure out ;)
    – Sean Earp
    Jun 20, 2009 at 6:37
  • I am not a programmer. I have done this. It is not easy, nor is it fun. After a good long while I was able to populate data from our MSCRM into a sharepoint list. I used the Meta-man, it's a good tool, as it the Microsoft BDC software. It takes a while to get everything in order.
    – RateControl
    Aug 31, 2009 at 20:36
1

I would think you could:

  1. Create an Excel spreadsheet that has the SQL query to the database
  2. Execute the query and do a Copy Values copy in Excel and paste that to a new workbook
  3. Import that workbook when creating the Sharepoint list

For displaying data live using a query, you need the Sharepoint Designer and you add a Data View to the list. See:

http://office.microsoft.com/en-us/sharepointdesigner/HA100948041033.aspx

Improve this answer
2
  • 1
    Thanks Kevin- That would certainly work for a one-time population, but the reason I am doing this is to keep a column in my list always synced with an external master data source. I will update my question to reflect that :)
    – Sean Earp
    Jun 19, 2009 at 20:01
  • I added a link that should guide you through adding a Data View to your Sharepoint list. Should work also in WSS or MOSS
    – Kevin Kuphal
    Jun 19, 2009 at 20:14
1

I took some time investigating this. In SP 2010 it is easy to import a table as an EXTERNAL SharePoint list. The data in the db and the list and is syncronized dynamically when someone modifies the table in the DB or the List in SP.

But trying to create a workflow that writes data to and external SharePoint list...well you can do it but it involves a convoluted process that utilizes DB triggers and SQL CLR Assembly Stub.

See: http://blogs.msdn.com/b/chaks/archive/2011/05/02/concept-leverage-sharepoint-workflows-with-external-lists-part-1.aspx for information on External Lists ans workflows.

Improve this answer
0

If specifically wanting to populate info from SQL Server 2008. You can use SQL Server's Integration Services (SSIS)

http://www.onlinetechblog.com/blog/index.php/2009/07/how-import-data-to-sharepoint-list-using-ssis/

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .