Error starting sql agent job from stored proc

Question

Error starting sql agent job from stored proc

ptaylor-1046289

SSC Enthusiast

Points: 126
More actions
March 20, 2009 at 12:53 pm

#384176

Hi,
I am using sql server 2005 standard edition SP3, and also have sql express 2005 running on a second PC. I have a snapshot replication job that runs on demand, it works fine, but I wanted to fire that demand via a web browser.
I found out how to write the stored procedure to call that agent job, and it executes no problem from within the sql manager studio. But when I call it via ASP, it fails with the error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
SQLServerAgent Error: Request to run job "jobname" (from User sa) refused because the job already has a pending request from User sa.
Can anyone help me get around this? Here is the code in the stored proc I am calling from the webpage:
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spcallreplication]
as
begin
EXEC msdb.dbo.sp_start_job N'SEEIP-SERVER-PADS31-Second Test-DESKTOP-17';
end
The calling of the stored proc from ASP is pretty standard, no parameters passed or anything.
I have made sure the stored proc and agent job are executing with appropriate permissions...
Thanks.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

So, I'm not sure what your problem may be, it may be related to the 2 hop rule, Search this site and you'll see a few posts/articles about this. You can read more about it here.

But the thing that concerns me is that it looks like you are letting your Web application connect to your database as SA. That is really not a good idea and you may find yourself rooted in no time if you deploy something like that. You're basically giving away the keys to the kingdom.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

ptaylor-1046289 SSC Enthusiast Points: 126 More actions · Answer 2

Thanks...

I have only set the ADO connection to use the sa account for testing... trying to get this working as I wasn't sure if this was a permissions issue or not. I don't intend to deploy this with it set like that.

Thanks for the link...will check it out.

Paul

noeld SSC Guru Points: 96590 More actions · Answer 3

A job can only be started if it is not already running you should check the job status first or you will get an error.

* Noel

ptaylor-1046289 SSC Enthusiast Points: 126 More actions · Answer 4

Thanks guys, I found another way around it.

I wanted the replication job to run everytime there was an update or insert on a table in the parent sql server, so I created a trigger that executes the job upon any insert or update against that table - works like a charm.

noeld SSC Guru Points: 96590 More actions · Answer 5

ptaylor (3/23/2009)
Thanks guys, I found another way around it.
I wanted the replication job to run everytime there was an update or insert on a table in the parent sql server, so I created a trigger that executes the job upon any insert or update against that table - works like a charm.

As long as the insert/updates are not close in time that will do it, but if you start inserting and/or updating frequently the call to start_job will fail.

* Noel

ptaylor-1046289 SSC Enthusiast Points: 126 More actions · Answer 6

Thanks...

At this point the updates will be infrequent, but thanks, I will keep that in mind if it scales out.

Might there be a better way to do it than this? I realize that snapshot replications can take time, as the tables grow...

Paul

noeld SSC Guru Points: 96590 More actions · Answer 7

ptaylor (3/24/2009)
Thanks...
At this point the updates will be infrequent, but thanks, I will keep that in mind if it scales out.
Might there be a better way to do it than this? I realize that snapshot replications can take time, as the tables grow...
Paul

maybe you want to switch to "transactional" replication at that point 😉

* Noel