November 15, 2005 at 1:32 pm
Hello,
I am trying to set up job to run a stored procedure with 2 parameters but it always fails. When I execute the same stored procedure in Query Analyzer it works. The stored procedure does return some errors in the Query Analyzer but it continues until it is done.
Here is the error message I get when I run the stored procedure as a job:
The job failed. The Job was invoked by User ROCKFORD\Tvandriest. The last step to run was step 1 (Run sp_assign_delegate).
Here is the error message I get from the Query Analyzer:
Server: Msg 2627, Level 14, State 1, Procedure Sp_Assign_Delegate, Line 108
Violation of PRIMARY KEY constraint 'webdelegates_pk'. Cannot insert duplicate key in object 'webdelegates'.
The statement has been terminated.
This error does not stop the stored procedure from finishing.
Thanks,
Tim
November 16, 2005 at 5:58 am
IMHO what you should search for is why the primary key violation occurs and adjust the code, so that it does not happen any more. Not sure what you mean by "does not stop from finishing", and also I have no idea what that procedure does - but anyway running a code that causes PK violation is not a good idea.
Generally, if the procedure contains several statements, and one of them terminates with primary key violation, only this one statement is terminated and the other statements execute. If you put this procedure into a job, precisely the same happens : the part that has problems is terminated, the rest executes - but job outcome in this case is job failed. However, that does not imply that the job did nothing or failed completely - understand it as "something is not working good".
HTH, Vladan
A simple example:
create table tst (pk int not null, txt varchar(30))
alter table tst
add constraint pk1 primary key (pk)
---------------------------------------
create procedure test_error
as
/*this will cause violation in subsequent runs*/
insert into tst (pk, txt) values (1,'just a test')
/*this will execute always*/
insert into tst (pk,txt)
select max(pk)+1,'this works'
from tst
---------------------------------------
exec test_error
---------------------------------------
select * from tst
drop table tst
drop procedure test_error
Create the table. Run procedure - it will insert 2 rows, no error. Run procedure again - it will terminate with violation, but the row with "this works" will be inserted anyway. Make a simple job that only starts this proc. Start the job. Job will fail, but another row with "this works" was inserted by the job.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply