CODEWITHSUNDEEP
sql-servervbscript
Jon Rose
Jon Rose

Reputation: 139

How to run a Microsoft SQL Server Agent Job from VBScript

I have an ERP system that allows me to attach VBScript to a button on a customized window. I would like to be able to run this script so that it fires off a SQL Server Agent Job on the server (SQL Server 2008). I've been looking in to this for a couple hours now and the closest thing I could see was a short script which seems to use a depreciated command (SQL.DMO). Here's the code I swiped from the web:

On Error Goto 0: Main() 
Sub Main() 
  Set objSQL = CreateObject("SQLDMO.SQLServer")
  ' Leave as trusted connection
  objSQL.LoginSecure = True
  ' Change to match the name of your SQL server
  objSQL.Connect "Server Name"
  Set objJob = objSQL.JobServer
  For each job in objJob.Jobs
   if instr(1,job.Name,"Job Name") > 0 then
     msgbox job.Name
     job.Start
     msgbox "Job Started"
   end if
  Next
End Sub

The resulting error is:

Line:  3
Char:  3
Error:  ActiveX component can't create object:  'SQLDMO.SQLServer'
Code:  800A01AD
Source:  Microsoft VBScript runtime error

Upvotes: 3

Views: 3089

Answers (1)

Serge
Serge

Reputation: 4036

Use sqlcmd and execute sp_start_job command:

Set oShell = CreateObject ("WScript.Shell")
oShell.run "sqlcmd -S localhost -E -Q ""EXECUTE msdb.dbo.sp_start_job N'My Job Name'""

sqlcmd should exist on the SQL Server.

Note:

This is a potential a security risk. As per documentation, by default, only members of sysadmin role can execute sp_start_job. You will need your VBScript to run under security context of a user that has sysadmin privileges in the SQL Server (which is not good).

Use Task Scheduler and a trigger file

Rather than starting the SQL Agent Job directly, create a Scheduled Task on the SQL Server that would:

  • be scheduled to run every 10 minutes

  • execute a cmd script, which would:

    • check for existence of a trigger file in a specified location
    • if file is found, would start the SQL Agent Job using sqlcmd, and delete the trigger file thereafter

The VBScript triggered by the ERP system should simply place an empty correctly named trigger file in the specified location.

Use a stored procedure to control what is being started

To further reduce attack vectors, you can create your own wrapper stored procedure in SQL Server that would start the required SQL Agent Job.

  1. Create a server Login:
USE [master]
GO
CREATE LOGIN [erp_trigger_user] WITH PASSWORD=N'pwd', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
  1. Create a database user:
USE [msdb]
GO
CREATE USER [erp_trigger_user] FOR LOGIN [erp_trigger_user] WITH DEFAULT_SCHEMA=[dbo]
GO
  1. Create a stored procedure that will start your required SQL Server Agent Job:

Note: the stored procedure will execute as dbo user, which has permission to execute msdb.dbo.sp_start_job.

CREATE PROCEDURE dbo.sp_StartERPJob
WITH EXECUTE AS 'dbo'
AS
BEGIN

    EXECUTE msdb.dbo.sp_start_job N'My Job Name'

END
  1. Grant the user permissions to execute the stored procedure:
GO
GRANT EXECUTE ON [dbo].[sp_StartERPJob] TO [erp_trigger_user]
GO
  1. Update your Scheduled Task to execute a Windows batch file that looks something like this:
if exist trigger_file.txt (
    sqlcmd -S localhost -E -Q -U erp_trigger_user -P pwd "EXECUTE msdb.dbo.sp_StartERPJob"
    del trigger_file.txt
) else (
    rem file doesn't exist
)

Upvotes: 3

Related Questions