locked
How to allow a third party to update a table on a SQL Server 2k5 db? RRS feed

  • Question

  • I need to allow a third party to update a table on a SQL Server 2k5 db. Sometimes, the updates can be large, other times, just a few lines.

    Since I have limited access to other tools, I think I should use the Sharepoint 2k7 system available to me. I also can't write custom web parts. The only way I'm allowed to interact with SP programmatically is through SP Web Services or SSIS.

    My strategy so far is to create an Excel template and attach it to a SP library. So, my user would click on "Add", fill in the spreadsheet, then submit it. At that point, I need to validate the sheet and post it to my db, sending message(s) to the originator about the success or failure of the process, including detailed error messages.

    I have in the past written a C# program (not my daily use language, but...) to identify items in the SP library that have not been processed, and using SP Web services, process the spreadsheet and change certain columns that prevent it from being reprocessed.

    I feel like there must be a better way using SP workflows. Maybe not. What ideas might you guys have? I'd like this to be done without human intervention wherever possible.


    Jim
    • Edited by Mike Walsh FIN Tuesday, January 17, 2012 3:30 PM Original Title of "Somewhat Newbie Strategy Question " said nothing at all about the problem. "Somewhat newbie" especially not.
    Tuesday, January 17, 2012 3:20 PM

Answers

  • Hello Jims-Indy,

     

    Thank you for your forum posting. I have reviewed your forum posting and the requirements you're looking at can't be handled with any workflow SharePoint or SharePoint Designer can build Out of the box. What you're looking to do would require 2 custom actions.

     

    1. An action that will open spreadsheet and verify the data within it

    2. An action that will allow you to specify a username / password and write to SQL table.

     

    Both of these would require some coding to be done.

     

    If above does not help you resolve the issue, you may consider to open a support ticket with Microsoft -  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone. The option you will need under this would be the Advisory services option, you also have the option of finding a Microsoft Gold Partner to work on this for you.

     

    However, other members of the community may still have encountered the issue you're seeing, and have a solution to offer!

    Regards,

    Jing Wang | Microsoft Online Community Support
    Tuesday, January 24, 2012 10:13 PM

All replies

  • Hello Jims-Indy,

     

    Thank you for your forum posting. I have reviewed your forum posting and the requirements you're looking at can't be handled with any workflow SharePoint or SharePoint Designer can build Out of the box. What you're looking to do would require 2 custom actions.

     

    1. An action that will open spreadsheet and verify the data within it

    2. An action that will allow you to specify a username / password and write to SQL table.

     

    Both of these would require some coding to be done.

     

    If above does not help you resolve the issue, you may consider to open a support ticket with Microsoft -  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone. The option you will need under this would be the Advisory services option, you also have the option of finding a Microsoft Gold Partner to work on this for you.

     

    However, other members of the community may still have encountered the issue you're seeing, and have a solution to offer!

    Regards,

    Jing Wang | Microsoft Online Community Support
    Tuesday, January 24, 2012 10:13 PM
  • Jim,

    In my research, you're certainly right that I could do this with a custom action. It appears to be fairly straightforward!

    I have Visual Studio 2005, and I have VS2008 (BIDS only...) on my client-provided computer, but with neither VS can I find workflow tools.

    I have asked my client to provide me with the appropriate tools to create those custom actions. I am limited to what they will provide me. I'll wait until they tell me no.

    In the meantime, I'm writing the code to process the worksheet using C# on the assumption I'll be calling it from the custom action.

    Thanks for your reply.

     

     


    Jim
    Tuesday, January 24, 2012 10:29 PM