Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62

    Unanswered: stored procedure issues

    hello
    i have a main nested stored procedure that calls another two procs (theses two procs populates some tables)
    the problem is when i run these two procs manually in the query analyser it takes about 2 min.
    but it takes about twelve hours to execute them from main proc via sql server agent job.
    can anybody give me an idea of what could be the issue???

    Thank you


    set nocount on

    begin transaction
    begin
    declare @result int
    exec @result = sp_getapplock @resource = 'worldship', @lockmode = 'exclusive', @lockowner = 'session' , @locktimeout = '0';

    if @result not in ( 0, 1 ) -- only successful return codes
    begin
    print @result
    raiserror ( 'lock failed to acquire.', 16, 1 )
    end
    else
    begin
    exec picktickettransfer;
    exec shipperdatatransfer;

    exec @result = sp_releaseapplock @resource = 'worldship', @lockowner = 'session';
    end
    end
    commit transaction;
    go
    Last edited by ypal; 08-07-08 at 16:03.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are there other processes at the time when your job runs, that access the same resources as those stored procedures? And if there are, - are they following the same concept of application locking? I suspect they are not, and since you're virtually asking for the lock timeout to be indefinite by specifying '0' instead of just 0, the default @@lock_timeout value is being used, which is -1 (indefinite). So my extrapolation is that one of your procedures runs into resource locking initiated by some other process, and that locking lasts...awhile
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2007
    Location
    Big City
    Posts
    62
    I have to check the other processies and when and how they runs. I know for sure there are processies.... They weren't written by me , I'm just trying to find a solution. How I may check which proc runs into resource locking initiated by some other process??? Shall I fix the @locktimeout = '0'; to @locktimeout = 0; ???

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •