Post reply

Stored Procedure Job Status

  • February 10, 2004 at 9:56 pm

    #178206

    Hi All,

    I have a stored procedure which is rather large. The stored proc is run from query analyser by the end users. I would be greatful if I could be given direction with a couple of issues I have come up with:

    Issue 1

    How can I output informational messages at certain points in the stored procedure. So users know that the job(which can take up to 50 minutes) is chugging along.

    Issue 2

    Which I believe is related....I tried to segment the different tasks in the stored procedure with Begin Transaction <sql> End Transaction and placed a print statement at the beginning of each transaction....am I way off in my thinking?

    Also, is there a way to stop the sql syntax of a stored procedure being checked before compilation? One step in this stored proc is to drop and recreate an index on a table.

    Kind Regards,

     

    Terry Pino

  • February 11, 2004 at 12:40 am

    #493556

    No, yes and no.

    Issue 1 - No, you can't output like you want in any easy manner.

    When a batch is run in QA (like exec myProc), results will be returned either when the batch ends,

    or when the buffer needs to be flushed. (I think ~4kb worth of resultset, but don't take my word for it)

    If the proc takes about 50 mins to run normally, just instruct your users to wait for the 50 mins -

    it's only after that that any concerns might be raised anyway, right?

    I'm however more concerned about the fact that end users are using QA as a tool.

    There may be resons for this, but it makes my skin crawl.. =;o)

    Issue 2 - If your thinking is to solve issue 1 by the print statements, se Issue 1 why this won't work.

    To stop syntax checking..?

    Well, why on earth would that be possible? =;o)

    No, it's not, and it shouldn't be either. (no offense)

    If your drop/create index step gives you troubles, you have to work around it following the rulse given for stored procedure programming.

    There's plenty of good info on procs to read in BOL.

    =;o)

    /Kenneth

  • February 11, 2004 at 11:23 pm

    #493744

    Thanks Ken,

    I'm trying a different approach using DTS...although I'm coming across a few dramas already in regards to using Global variables.

    So look out for my post in the DTS section.

     

    Cheers,

    Terry

  • February 12, 2004 at 6:32 pm

    #493918

    If you really wanted to you can.  Just create a table like "sp_Status".  In your sp, just insert a record into sp_Status with some description at whatever steps/points you want.  But, you would have to ask the user to open another QA window and do a "SELECT * FROM sp_Status" every now and then to see what's the status.  So, the sp_Status Table might look like

    Step   Description

    1        Finished copying Data

    2        Fixing Data

    3        Archiving Data

    ...

     

     

     

     

  • February 12, 2004 at 6:49 pm

    #493919

    I would remove QA from the users and give them an ASP page. From this page they could launch and monitor the process.

    Less chance of things going haywire because the changed the SQL statement or quiered the wrong table.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 12, 2004 at 7:17 pm

    #493920

    Ooooh....I like that suggestion...Tell me more...do you have a good starting point(other than BOL cause that where Im looking now).

     

    Cheers,

     

    Terry

  • February 12, 2004 at 7:28 pm

    #493921

    Try any of the ASP/Dev webistes.

    http://www.asp.net
http://www.15seconds.com/
http://www.4guysfromrolla.com/
    etc...

    --------------------
    Colt 45 - the original point and click interface

Viewing 7 posts - 1 through 6 (of 6 total)

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