none
Execute SQL Job by clicking on promoted link

    Question

  • Hi

    I'm looking for a way to be able to start a SQL Server job from Sharepoint Online. My idea is that a user can click on one of the promoted links (click to update budget), that then triggers a job on a sql server.

    I know how to handle the SQL Server in regards to SQLagentOperator Rights and starting the job by executing the following SQL

    EXEC MSDB.dbo.sp_start_job @job_name= 'JobName'

    But how can i trigger it ?

    Wednesday, January 25, 2017 11:44 AM

All replies

  • Hi,

    Are you using local SQL Server?

    You can create a custom service to access local SQL Server, which executes job you needed. When users click link from web front side, it calls your custom service to exe SQL Server job.

    However, it’s not recommended to make SQL Server available to all users who can access the SharePoint page, which may cause security issues.

    Thanks,

    Dean Wang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, January 26, 2017 7:56 AM
    Moderator
  • Hi Dean

    It's SharePoint Online in Office 365 so it's not a local SQL Server but a SQL Server that i'm running in VM also in Azure. My idea is to create a SQL login that is only allowed to execute a job. Alternative reduce the login to be able to update a row in a table from which a pooling Query running every 10 minuttes will pick up that "NeedUpdate" column value have been set to 1 which then leads to a execution of the package.

    Thursday, January 26, 2017 8:03 AM
  • Hi,

    I suggest you create a Provider-Hosted app to achieve your requirement for SQL Server in Azure.

    A detailed demo about how to create Provider Hosted SharePoint Add-In With SQL Database in Azure for your reference:

    http://www.c-sharpcorner.com/UploadFile/a30324/provider-hosted-sharepoint-add-in-with-sql-database-in-azure/

    It won't be an easy job to make all these things working together, therefore, a fact you might want to take into consideration is that more time and effort would be required.

    Thanks,

    Dean Wang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, February 3, 2017 9:15 AM
    Moderator