We help IT Professionals succeed at work.

Run SQL Store Procedure using Excel VBA

Eric Harris
Eric Harris asked
on
Hi there,

I need to run a store procedure or SQL job from within excel.
I have a storeprocedure which dies some extensive data manipulation and creates a summarized table.

I then use this table to run queries with excel. (No problem ith this.

I'd like to be able to run the stored procedure in vbas priot to doing my query refresh.

I've looked on varios discusions in ~EE and have tried a few.

The last one suggested putting my sp in a job and then running the job.

I'm obviously doing something wrong but can someone please help

Here's my vba code to run the SQL job


Dim c As ADODB.Connection
Set c = New ADODB.Connection
'c.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=EB_Production;Data Source=EBDBSQL\EBSQL;PWD=3ntri3$$"
c.Open "DRIVER=SQL Server;SERVER=EBDBSQL\EBSQL;UID=sa;PWD=3ntri3$$;APP=Microsoft Office 2003;WSID=EBDBSQL;DATABASE=EB_Production"
c.Execute "exec msdb.dbo.sp_start_job @job_name = 'Packing_Invoicing' "
c.Close
Set c = Nothing


also here's an example connection string from one of the queries. Don't know if that's relevant

DRIVER=SQL Server;SERVER=EBDBSQL\EBSQL;UID=sa;APP=Microsoft Office 2003;WSID=EBDBSQL;DATABASE=EB_Production
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT
Commented:
I'd do this.

Dim c As ADODB.Connection
Dim comm as ADODB.Command

Set c = New ADODB.Connection
c.Open "DRIVER=SQL Server;SERVER=EBDBSQL\EBSQL;UID=sa;PWD=3ntri3$$;APP=Microsoft Office 2003;WSID=EBDBSQL;DATABASE=EB_Production"

Set comm = New ADODB.Command

With comm
    .ActiveConnection = c
    .CommandTimeout = 600
    .CommandType = adCmdStoredProc
    .Prepared = True
    .CommandText = "msdb.dbo.sp_start_job"
    .Parameters.Append .CreateParameter("@job_name", adVarChar, adParamInput, 255, 'Packing_Invoicing')
    .Execute
  End With

c.Close

if not is nothing then set comm = nothing ' check for nothing before setting to nothing to prevent runtime errors if they are not created properly
If Not is Nothing then Set c = Nothing
Eric HarrisDeveloper

Author

Commented:
Thanks for that.
I tried it and I'm getting an syntax error on the .parameters line
Lee Savidge
CERTIFIED EXPERT

Commented:
Whoops...

Change:

'Packing_Invoicing'

To

"Packing_Invoicing"