Link to home
Create AccountLog in
Avatar of newbieal
newbiealFlag for United States of America

asked on

asp .net: call a sql job?

Can a sql job be initiated via a asp .net page?  I want to just provide a button to the user, and when clicked will execute the sql job.  If this is possible, what is the code for the initiating of the sql job?
Avatar of chapmandew
chapmandew
Flag of United States of America image

sure...its just a TSQL call, but the account will need the permissions to do it...


EXEC dbo.sp_start_job N'Weekly Sales Data Backup' ;
Avatar of newbieal

ASKER

I need to do this via my asp .net application.  The code just shows me how to do it in SQL, correct?  The job is already in place, but how do I call/execute it via my asp .net app?
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I get this error when creating this sp:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.sp_start_job'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

The job in mgmt studio is listed under SQL Server Agent --> jobs

What am I missing?
Try it now...the proc is in msdb database

alter procedure usp_runjob
as
EXEC msdb.dbo.sp_start_job N'Weekly Sales Data Backup'
I get the same message. I browsed programmability for msdb and the sp is not there.
Are you using SQL 2005?
yes, i went into the job and said script to new query window.  What does that do?
just scripts the job out....


did you look in the system stored procedures folder in msdb?  I just checked and it is definetly there....
No, I checked, it's not there.
Do you have SQL Agent running?
Yes, that's where I can see the job
well, that is how sql agent runs jobs....so, the proc has to exist....
Any other way to look for it other than browse?  I cannot find it by browsing.
sure...view this in text

use msdb
select object_definition(object_id('sp_start_job'))
It says NULL
Ahhh...you're not a sysadmin on the machine, are you?  Make sure someone adds you to the SQLAgentOperatorRole  role in msdb.