locked
How to automatically update sharepoint list column with sql server data once a new listitem is created in the list RRS feed

  • Question

  • I have a Sharepoint list, I have a column in list called Manager which needs to be updated with sql server column once a new record is created in the list. the manager data is coming from SQL Server, based on the column "created by" in sharepoint list.

    For eg: CreatedBy is an employee and we need to get his manager from Sql Server.

    Is it possible through SP Designer.

    Kindly help

    Tuesday, October 11, 2011 2:19 PM

Answers

  • Hello Ravi,

    You can use BDC to achieve this, for more info please take a look @ 

    http://serverfault.com/questions/28851/how-do-i-populate-a-sharepoint-list-with-data-from-an-external-sql-database

    or another option mentioned in the same article is 

     

    • Create an Excel spreadsheet that has the SQL query to the database
    • Execute the query and do a Copy Values copy in Excel and paste that to a new workbook
    • 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/sharepoint-designer-help/create-a-data-view-HA010094804.aspx

    hth

     

     


    Warm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/ | Posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Pengyu Zhao Wednesday, October 19, 2011 8:37 AM
    Wednesday, October 12, 2011 12:08 AM
  • I'd point out, that the BCS offering for SharePoint 2007 is a one way read only implementation.  You're not going to get to be able to write back.

    You'll also need the Enterprise version of SharePoint 2007, so you're out of luck with either WSS or MOSS Standard.


    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    • Marked as answer by Pengyu Zhao Wednesday, October 19, 2011 8:37 AM
    Wednesday, October 12, 2011 8:37 AM
    Answerer

All replies

  • Hello Ravi,

    You can use BDC to achieve this, for more info please take a look @ 

    http://serverfault.com/questions/28851/how-do-i-populate-a-sharepoint-list-with-data-from-an-external-sql-database

    or another option mentioned in the same article is 

     

    • Create an Excel spreadsheet that has the SQL query to the database
    • Execute the query and do a Copy Values copy in Excel and paste that to a new workbook
    • 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/sharepoint-designer-help/create-a-data-view-HA010094804.aspx

    hth

     

     


    Warm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/ | Posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Pengyu Zhao Wednesday, October 19, 2011 8:37 AM
    Wednesday, October 12, 2011 12:08 AM
  • I'd point out, that the BCS offering for SharePoint 2007 is a one way read only implementation.  You're not going to get to be able to write back.

    You'll also need the Enterprise version of SharePoint 2007, so you're out of luck with either WSS or MOSS Standard.


    Steven Andrews | SharePoint Professional | http://www.twitter.com/backpackerd00d | https://baron72.wordpress.com/
    • Marked as answer by Pengyu Zhao Wednesday, October 19, 2011 8:37 AM
    Wednesday, October 12, 2011 8:37 AM
    Answerer