Post reply

Trouble running stored proc with a job

  • July 7, 2004 at 9:04 am

    #83302

    I have a stored procedure that completes in 3 minutes when run through query analyzer, but when I put the stored proc in a job (either with the entired text of the stored procedure as a step or using exec stored_proc_name), the job runs endlessly.  I have run a trace and examined the execution plans and they appear to be identical.  The stored proc is doing an update on 20,000 rows and just creeps along when run through the job.  Any ideas on what could be causing this or how I can track down the problem?  Any ideas are welcomed!

  • July 8, 2004 at 12:10 am

    #513790

    From a search on Google Groups:

     

    Try SET NOCOUNT ON before starting the cursor

    while loop...

    ...and if SQLAgent doesn't honor NOCOUNT (due to a bug), run the script from

    OSQL.EXE.

     I just ran into the same scenario. I had a sp that from the Query window

    took about 7 secs and as a scheduled job it took over 5 minutes. Someone

    told me to run it as a scheduled osql job instead and that brought it back

    down to the correct time. I still don't know why yet but it worked.

     

  • July 8, 2004 at 12:24 pm

    #513906

    A great suggestion, but this did not solve my problem!  Any other ideas?

    Thanks!

    Anne

     

  • July 8, 2004 at 12:29 pm

    #513907

    Hi Anne,

    Could you send the text of the sql script?  It would help also to have the schema info(create table statement).

    thanks,

     

    bob

     

  • July 8, 2004 at 12:40 pm

    #513909

    Put the SQL in a text file and use the /i parameter of OSQL to specify the inputfile.

    See the documentation about OSQL.EXE and the other switches. So, your CmdExec job will

    look something like:

    OSQL /E /isqlfile.sql /ooutfile.out

Viewing 5 posts - 1 through 4 (of 4 total)

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