Excel VBA - Executing a job within SQL Server via a macro

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?

Solution 1

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

Solution 2

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

Comments

[Dean McGarrigle]

This seems to work, but i am getting a permissions error when i run the macro (the credentials i passed are correct). Execute permission denied on object sp_start_job. Any thoughts?

[Andomar]

The credentials must have rights to start a job. Any sysadmin account will work. Otherwise, see these DBA answers

[Dean McGarrigle]

The account i am trying to use is indeed a sysadmin account. Only seems to work on the local PC, when i try to execute the job from anywhere else i just get that same error. I'll keep trying...

[Andomar]

The example uses SQL logins. To login with your windows credentials, replace USER=YourUser;PASSWORD=YourPassword; with Integrated Security=SSPI;

[Hairy Drumroll]

This seems to work, but what if I want to "Start Job at Step..."?