Excel VBA - Executing a job within SQL Server via

Question

I have a job stored on a database, scheduled to run every day. But its sometimes necessary to want to execute this job at any given time to view up to date data (I'm using SQL Server Management Studio 2008).

The job itself simply takes data from a view which contains live data and puts it into a table which will then be used as a data source for an excel file. Executing the job drops and re-creates the table with fresh data.

In excel (2010), i wish to have a 'button' which which pressed will execute the job and then hitting refresh on the data tab in excel will then update the data on the sheet with the fresh data.

My question is: How do i execute this job from an excel macro?

Answer 1

You can use SQLDMO.SQLServer to execute your job.

Answer 2

You create a SP that moves data from view to table. Then modify the Job that it executes that SP by schedule. Then in Excel Macro you can just use that SP to update the data.

Or see example how to run the Job from VBScript

Answer 3

Private Sub CmdRunJob_Click()
   Dim con As Object
   Set con = CreateObject("ADODB.Connection")
   con.Open = "DRIVER={SQL Server};SERVER=YourServer;" & _
       "USER=YourUser;PASSWORD=YourPassword;"
   con.Execute "exec msdb.dbo.sp_start_job 'YourJob'"
End Sub