SQL Server Performance

how to check if is a system SP returns any rows?

Discussion in 'General Developer Questions' started by chopeen, Sep 7, 2005.

  1. chopeen Member

    What is the best way to check if a system stored procedure - for example, sp_help_job (with some arguments that I don't put here, because I don't want to complicate it too much) or sp_who - has returned any rows?

    I know I can use INSERT INTO #table EXEC dbo.sp_who and then count the rows, but it is way to complicated.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  2. Chappy New Member

    INSERT INTO is the only way I know if.
    Depending on what sort of information you need from the stored procs, you might be able to bypass them and access the INFORMATION_SCHEMA views

    As a last resort you could use the system tables, but this is not really recommended unless you had big performance problems using the INSERT method
  3. FrankKalis Moderator

    I would agree with Chappy that I am not aware of any other way. Why is it too complicated?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. benwilson New Member

    I am sure i must be missing something here, but why wouldn't SELECT @@ROWCOUNT let you know if any rows have been returned?

    Ben

    'I reject your reality and substitute my own' - Adam Savage
  5. Madhivanan Moderator

    >>I know I can use INSERT INTO #table EXEC dbo.sp_who and then count the rows, but it is way to complicated.

    What kind of complication?

    Madhivanan

    Failing to plan is Planning to fail
  6. Chappy New Member

    quote:Originally posted by benwilson

    I am sure i must be missing something here, but why wouldn't SELECT @@ROWCOUNT let you know if any rows have been returned?

    I tried this too. It just returns 1 regardless of the output from the system proc
  7. ranjitjain New Member

    I've noticed that whenever we do insert or update on temporary tables the @@rowcount returns 1 if it doesn't insert or update anything it returns 0 is it??????????
  8. Adriaan New Member

    Since you're inserting the resultset into a temporary table, why not use this:

    IF EXISTS (SELECT * FROM #table WHERE <not-nullable column> IS NOT NULL)
    BEGIN
    --
    END
  9. chopeen Member

    quote:Originally posted by FrankKalis

    I would agree with Chappy that I am not aware of any other way. Why is it too complicated?
    Because I only want to know if any rows have been returned. And to do this I need to explicitly create a table and define its structure. Isn't that too much fuss?

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  10. FrankKalis Moderator

    Well, yes and no. You don't have much alternatives, do you? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. chopeen Member

    I've just realized that it may be a good idea to describe what my problem exactly is. Maybe there's another way to work this out.

    I need to check (using SQL) whether a job is executing or not.
    I want to use sp_help_job, but maybe this is not the only way.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  12. ranjitjain New Member

    Chopeen,
    I think to check existance of records you need to create a temp table though its too much fuss.
    Go for @@rowcount >0 as you dont need actual number of rows inserted.
  13. Adriaan New Member

    With the usual caveat about querying system tables directly ...

    SELECT j.name
    FROM msdb.dbo.sysjobhistory jh
    INNER JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    WHERE jh.run_status = 4 -- 'in progress'
    GROUP BY j.name
  14. chopeen Member

    quote:Originally posted by ranjitjain

    I think to check existance of records you need to create a temp table though its too much fuss.
    Go for @@rowcount >0 as you dont need actual number of rows inserted.
    Read all posts in this thread. This method doesn't work. Unfortunately.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  15. chopeen Member

    Thanks, Adriaan. I guess I will use the method you suggested.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  16. chopeen Member

    INSERT INTO #temp EXEC msdb..sp_help_job does not work in this case.

    It fails with such an error:
    An INSERT EXEC statement cannot be nested.

    The explanation is here:
    http://www.sommarskog.se/share_data.html#INSERTEXEC

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  17. Adriaan New Member

    In my second posting on this thread, you will find a query on msdb.dbo.sysjobs and msdb.dbo.sysjobhistory, which should tell you if there are any jobs still in progress. No need for a temporary table, no problems with the system sp calling other sp's ...

    Does sp_help_job return specific information that you're using further on? Big chance that you can extract that information from the system tables too - with a little interpretation.
  18. chopeen Member

    quote:Originally posted by Adriaan

    In my second posting on this thread, you will find a query on msdb.dbo.sysjobs and msdb.dbo.sysjobhistory, which should tell you if there are any jobs still in progress. No need for a temporary table, no problems with the system sp calling other sp's ...
    Yeah, I know. I tested it a few days ago and it worked perfectly. But when I tried to use it today, for some reasons it did not show me that a job was running. No idea why. Maybe I'll have to give it a shot one more time.


    quote:Originally posted by Adriaan

    Does sp_help_job return specific information that you're using further on? Big chance that you can extract that information from the system tables too - with a little interpretation.
    Not at all. I merely need to know whether a job is running or not.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
  19. Adriaan New Member

    You could review the script of msdb.dbo.sp_help_jobs to see where it is retrieving the data, that might give you a hint where to look further.
  20. chopeen Member

    Thanks guys for all your help.

    A solved problem and this are the results.

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/

Share This Page