Sep 15, 2006

Missing sp_who

The issue I am going to write on is something I had seen on a website quite sometime back. I don't remember any explaination given about the behavior. I tried to search for that site now but in vain. So, I am posting it anyways. And I will also be writing about the attitude I get from the guys who are Anti-Microsoft. My thoughts on it will be intertwined in my post and hence might seem that I am digressing from the topic at times :)
 The issue is this - Lets take these two batches:

Batch #1

select * from sysobjects

Batch #2

select * from sysobjects

When I execute the first batch, it gives me two results sets. The result of the stored procedure and the query. But, if I run the second batch I get only the result of the query. The result of sp_who is missing. The question is why?

I had asked the same question to few of my colleagues, and they came back to me with a variety of answers, saying that it was an issue in SQL Server 2000, a bug. Some said, it's got to do with the way sp_who is implemented.

More often than not, we tend to complain on the environment when all it requires is some introspection (or, atleast, inspection in this case).

All over the world its "Innocent until proven guilty", but when it comes to Microsoft products, its always this attitude "Microsoft is guilty until proven innocent" :)

You install an application in Windows and run it and it hangs cause of a poor implementation. We just say that Windows hanged. There is even a song going around called "Windows 95 sucks".

But then, I ask, if it irritates you so much, why use it? Switch to something else? Frankly, Its too much of a pain for me to use anything else when life is so simple as of now. Let the project be implemented in whatever technology, it cannot survive a day without MS Office (My belief absolutely). Maybe, I don't know the world outside.

Anyways, coming back to the issue at hand, the reason for this wierd behavior of the second SQL batch is something to do with a best practice and reading through BOL.

Here is the line from the BOL

"You do not have to specify the EXECUTE keyword when executing stored procedures if the statement is the first one in a batch. "

So, If I rewrite the second batch this way it works fine.

select * from sysobjects
EXECUTE sp_who

Now, then the second question comes, if EXECUTE is required if the SP call is not the first statement, then why doesn't it throw an error?

It doesn't throw an error because, the statement is perfectly valid if we understand that new line character is not a delimiter for a SQL statement. Maybe rewriting the batch this way will help.

select * from sysobjects sp_who

If even this doesn't throw some light on the issue, this might.

select sp_who.* from sysobjects sp_who

Or maybe this..

select a.* from sysobjects a

To put it in words, sp_who was taken as an alias name for the table sysobjects.

Two things I would like to standardize here.
-Delimiting all T-SQL statements with a semi-colon will help.
-Execute all stored procedures with an EXEC whether its the first line or the last line of the batch.


Post a Comment