Linking sharepoint to a SQL Server job notification. Possible?

[JohnnyMCE]

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.

 

[bunker]

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

 

[KB]

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

 

[leeland]

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.

 

[Gunslinger08]

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.