Linking sharepoint to a SQL Server job notification. Possible?

Discussion in 'Programming' started by JohnnyMCE, Oct 28, 2011.

  1. JohnnyMCE

    JohnnyMCE Member

    Joined:
    Apr 13, 2006
    Messages:
    141
    Likes Received:
    0
    So I am kicking around an idea at work and before I bring it up to my boss and the sharepoint folks I want to know if it is even feasible.

    Here is the scenario. We have some SQL server jobs that run nightly that load data in the database. At the moment in the morning if the jobs succeed we receive an e-mail stating this then we have to log into the sharepoint site and update the status of the load manually. Basically just stating the date and time the load completed and if it did.

    Is there a way I can insert some code into the sharepoint site that will update automatically based upon a trigger of some sort from SQL Server?

    Not sure if this is possible but any thoughts or links would be greatly appreciated.
     
  2. Loading...

    Similar Threads - Linking sharepoint Server Forum Date
    Same code, same server, but PHP session not working? Programming Oct 11, 2017
    SQL Server help Programming Sep 21, 2017
    HTML Video Link Programming Sep 14, 2017
    Is it possible to send mail through an exchange server from Linux via php? Programming Jul 18, 2017
    link to a mdf database Programming May 24, 2017

  3. bunker

    bunker Lifer

    Joined:
    Apr 23, 2001
    Messages:
    10,578
    Likes Received:
    0
    If all your jobs are in a list in SharePoint you could use SSIS to get the status and run a Powershell script to update the list.

    Don't ask me how to do it though, I just went and asked my Sharepoint guy for you :p
     
  4. KB

    KB Diamond Member

    Joined:
    Nov 8, 1999
    Messages:
    4,717
    Likes Received:
    38
    SQL Server code can call just about any other code out there, .Net, batch files, powershell, so this is entirely doable. The problems are that you may need enable these features in SQL Server, also debugging t-sql or SSIS isn't a lot of fun. If I were given this task I would create a query to fetch the completed times of each job from the MSDB (sysjob, sysjobhistory tables) database in sql server. I would then have sharepoint fetch this data into a spreadsheet or list.

    Here is a sample script to get you started.
    http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx
     
  5. leeland

    leeland Diamond Member

    Joined:
    Dec 12, 2000
    Messages:
    3,655
    Likes Received:
    0
    depending on the complexity of the process involved with updating this sharepoint site I would conceive you could do something along the lines of adding a step to your job.

    The step could back end update the site in sharepoint...if this is as simple as flipping a flag it might not be that big of a deal.

    To find out where the update is occurring when you are in the site, you could run a profile trace on the sql server to capture the t-sql commands that are occurring when you are making the changes on the front end.

    Is this supported...probably not
    Is this a good idea...probably not

    However if the risk is deemed to be low and the update simple then I wouldn't totally crap on the idea.
     
  6. Gunslinger08

    Gunslinger08 Lifer

    Joined:
    Nov 18, 2001
    Messages:
    13,237
    Likes Received:
    0
    I know this is way after the fact, but you can do it fairly simply. Add a new email address in Exchange for SharePoint to receive updates on. Have your nightly job also send the email to this address. You can mail enable lists in SharePoint. Do that for the status list and write/attach a new SPEmailEventReceiver that parses the email body and updates the list appropriately. This approach removes the need for and SQL changes such as registering .net assemblies and such, for triggers and procedures to call.