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

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/

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

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

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

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??????????

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

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/

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/

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.

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

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/

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

--

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

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/

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.

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/

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.

Thanks guys for all your help.

A solved problem and this are the results.

--

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