December 11, 2003 at 8:28 am
Hello,
I am trying to find out how to schedule a SQL Server Agent job for immediate execution via a stored procedure or asp code. Basically, I want to call a job to execute immediately regardless of the schedules specified in the job. Is there a way to accomplish this?
I want to execute a DTS package on the server itself (not on a client machine or web server) by executing the job which calls the DTS package.
December 11, 2003 at 9:43 am
See sp_start_job in the BOL.
December 11, 2003 at 11:43 am
Duh. I don't know why I couldn't find this earlier. Thanks for the help!
December 11, 2003 at 1:45 pm
This still doesn't work. When I try it (from an account that is System Administrator on the SQL Server) I get the following error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @job_id ('190C3A02-E8D6-43B7-95CD-003F2F034B9C') does not exist
I can run this from Query Analyzer no problem, and I even logged in as the accout the web page is running from and I can execute the job in Enterprise Manager from that account.
This is driving me crazy, any help would be greatly appreciated.
December 11, 2003 at 2:01 pm
We do this all the time from IIS jobs and it works. What is the circumstances you are running when it fails, e.g. what is different?
December 12, 2003 at 12:40 pm
I am basically executing a SP from a web page. That stored procedure contains a line like EXEC msdb.dbo.sp_start_job @job_id = '190C3A02-E8D6-43B7-95CD-003F2F034B9C'
The user is is executing under is not a system administrator, but doen own the job. I even tried making the user a system administrator, and it still didn't work.
I cam log into Windows as the user and see and execute the job, so I know the account has the required security. I don't know what else to try...
December 14, 2003 at 7:47 pm
When you say you can log in as the user and execute it, are you doing that from Query Analyzer? If not, try it with exactly the same statement. Run query analyzer from the system that IIS is on.
And are you SURE that the user context in the web page is what you think it is? Does it have an explicit connection string that specifies the user for connection to the database, or does it use integrated authentication, and if the latter are you sure how it actually connects?
You might want to run SQL Profiler on the database at a (near) idle time, and capture all the connections and activity. This will let you see the actual execution of the start_job sp, and determine if the context in which you are running it is what you expect.
I've always ued the job name, not ID in the start job, but I would still expect it to work.
December 15, 2003 at 12:11 am
If you no longer want the jobownership-burden to be able to start a job, create an alert which you let start the job. So when you want to start your job, you raiserror the alert and your job will start ASAP.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply