Error when calling SP

[mkov]

I am posting this here because it seems to be more of a VBA issue than SQL.

I have a sp setup on a SQL Server that runs a job. I can call the sp from Query Analizer and it runs without a problem, but when I try calling the same sp from VBA, I get the following error:

Run-time error '-2147217900 (80040e14)'
The specified @job_name('NameOfJob') does not exist.

I have tried hard coding the name of the job in the sp and also passing the name as a parameter when calling it from VBA, and I get the same error each time.

VBA Code:
Set cnSQL = New ADODB.Connection
Set cmd = New ADODB.Command

cnSQL.Open _
"Provider= sqloledb;Driver=SQL Server;" & _
"Server=ServerName;" & _
"Database=CommissionManagement;UID=UserId;PWD=Password"

With cmd

.ActiveConnection = cnSQL
.CommandText = "procSunGardCommUpload"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@Job_Name", adVarChar, adParamInput, 30, "NameOfJob")
.Execute

'Closes and resets the recordset
cnSQL.Close
Set cmd = Nothing
End With

SQL sp:
CREATE PROCEDURE procNameOfsp

(
@Job_Name varchar(30)
)
AS

exec sp_start_job @Job_Name
GO

I am using Assess XP and SQL Server 2000

Any help would be appreciated.

Thanks.

 

[mkov]

Never mind, I figured it out.

Inorder for this to run, the user id I use to connect to SQL with must have the same permissions as the sa login or it won't work.

Thanks.