HMote


Does anyone know how to run a SQL Server Job in a VBA macro Thanks!




Re: How to run a SQL Server Job in VBA

Melissa H


I am trying to figure out how to do this myself. If I find it, I will post it.

Thanks.






Re: How to run a SQL Server Job in VBA

HMote

I'll do the same...if I ever figure this out.




Re: How to run a SQL Server Job in VBA

JLS123

I think you need to use a pass-through query. Paste something like this:

USE msdb
EXEC sp_start_job "Name of My Job"

in the query. Set the ODBC Connect String in the properties. Then run a macro that calls the query. Will that give you what you need

http://office.microsoft.com/assistance/hfws.aspx AssetID=HP051884041033&CTT=1&Origin=EC010227011033&QueryID=MgIBHDA1u0&respos=1&rt=2





Re: How to run a SQL Server Job in VBA

Melissa H

That worked like a charm! Thanks so much for your assistance.



Re: How to run a SQL Server Job in VBA

HMote

Melissa, could you post your VBA code and what you did I'm still having troubles with this...Thanks!



Re: How to run a SQL Server Job in VBA

HMote

Why won't this work

Set con1 = CreateObject("adodb.connection")
con1.Open "Provider=sqloledb; Data Source=DB; Initial Catalog=tb; User Id=id; Password=pass;"

Set cmd1 = CreateObject("adodb.command")
cmd1.ActiveConnection = con1

cmd1.CommandText = "USE msdb ; " & vbCrLf & _

"GO " & vbCrLf & _

"EXEC dbo.sp_start_job ExcelVBAJob' ; " & vbCrLf & _

"GO"

cmd1.Execute

Set con1 = Nothing





Re: How to run a SQL Server Job in VBA

JLS123

I see you're missing a single quote just before ExcelVBAJob in the line:

"EXEC dbo.sp_start_job ExcelVBAJob' ; " & vbCrLf & "GO"

The approach should work otherwise.





Re: How to run a SQL Server Job in VBA

Melissa H

Create new Query in Design View

Click Close at the Add Table box

Right Click in the Query Window and select SQL Specific - Pass Through

Type your SQL code:

USE msdb

EXEC dbo.sp_start_job ExcelVBAJob

Open the Query Properties and type in the connection info in the ODBC Connect Str field:

ODBC;DSN=msdb;Description=msdb;UID=id;PWD=pass;DATABASE=msdb;Network=DBMSSOCN;Address=bdc-cadence001,1833

Be sure to enter the right parameters for your situation(UID, PWD, DATABASE, NETWORK, ADDRESS)

Change the Returns Records properties to No

Close the Query & Save