Stored Procedure does not appear in the sysobjects table.

[Robin Tucker]

Hi,

I'm trying to determine with my program whether or not a given database
supports a given feature set. To do this I'm querying for certain stored
procedures in the sysobjects table and if they are present, making the
assumption the database will support the given feature. The problem is I
can't find a certain stored procedure in the sysobjects table, even though I
know it exists and can see other similar procedures using:

select * from dbo.sysobjects order by name

Its as if all of my other stored procedures are in the sysobjects table
except this one, the one I'm specifically querying for. Are there certain
reasons why a proc won't appear in the sysobjects table? Is this something
I need to fix?

Thanks,

Robin

[Simon Hayes]

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cs******** ***********@new s.demon.co.uk.. .

Hi,

I'm trying to determine with my program whether or not a given database
supports a given feature set. To do this I'm querying for certain stored
procedures in the sysobjects table and if they are present, making the
assumption the database will support the given feature. The problem is I
can't find a certain stored procedure in the sysobjects table, even though
I know it exists and can see other similar procedures using:

select * from dbo.sysobjects order by name

Its as if all of my other stored procedures are in the sysobjects table
except this one, the one I'm specifically querying for. Are there certain
reasons why a proc won't appear in the sysobjects table? Is this
something I need to fix?

Thanks,

Robin

Every object should be in sysobjects - if the proc isn't, then how do you
know it really does exist (you might need to refresh EM or the QA object
browser if that's where you're seeing it)? Is it a user proc or a system
proc (in which case it could be in msdb)? Can you execute it? Does
OBJECT_ID() return an ID for it? Does sp_helptext return the proc text?

You can run DBCC CHECKDB to check for any database integrity problems, and
if you have several very similar databases, don't forget to check you're
querying the correct sysobjects table.

Simon

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.com) writes:

I'm trying to determine with my program whether or not a given database
supports a given feature set. To do this I'm querying for certain
stored procedures in the sysobjects table and if they are present,
making the assumption the database will support the given feature. The
problem is I can't find a certain stored procedure in the sysobjects
table, even though I know it exists and can see other similar procedures
using:

select * from dbo.sysobjects order by name

Its as if all of my other stored procedures are in the sysobjects table
except this one, the one I'm specifically querying for. Are there
certain reasons why a proc won't appear in the sysobjects table? Is
this something I need to fix?

Sounds like there is some mishap with owner. Or a surprising character
somewhere. i and í are not too easy to discern.

The best way to check whether a stored procedure exists is with

IF object_id('your procedure', 'P') IS NOT NULL

While querying system tables and INFORMATION_SCH EMA is good for admin
and maintenance stuff, I think one should be wary of doing in application
code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Robin Tucker]

Hmmmm. Ok, it was a user error (I was querying the wrong database). On the
other point, about being wary of querying system tables in an application,
is there anything imparticular I should be wary of? The way I see it, there
are two methods of determining if any given database (1) supports my
application and (2) supports the current version of my application.
Firstly, I could just execute the "abcd_GetVersio n" stored procedure. I
will get an exception if it fails, ergo: not a database my software can use.
Once I have established abcd_GetVersion can be executed, I can just execute
it to find the database version and everyone is happy. The second way is
to query the system tables for objects I know are unique to my schema/object
set (ie. looking for a specific stored procedure).

Now the issue I have is that I'm enumerating servers in a combo box on a
form and I have a checkbox which states "only show servers hosting a
compatible database". Actually attempting to connect, run the stored
procedure and fail on exception is an amazingly slow way of doing this.
Much simpler I think, to just check the sysobjects table to see if the given
stored procedure I need is there.

So heres what I would like to do: connect to the server. If the connection
fails, then I am not a compatible server (for whatever reason). Once
connected: query something, somewhere to find out if the server hosts a
certain kind of database. Will I have enumerate the entire list of
databases and check each one individually?

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Robin Tucker (id************ *************@r eallyidont.com) writes:

I'm trying to determine with my program whether or not a given database
supports a given feature set. To do this I'm querying for certain
stored procedures in the sysobjects table and if they are present,
making the assumption the database will support the given feature. The
problem is I can't find a certain stored procedure in the sysobjects
table, even though I know it exists and can see other similar procedures
using:

select * from dbo.sysobjects order by name

Its as if all of my other stored procedures are in the sysobjects table
except this one, the one I'm specifically querying for. Are there
certain reasons why a proc won't appear in the sysobjects table? Is
this something I need to fix?

Sounds like there is some mishap with owner. Or a surprising character
somewhere. i and í are not too easy to discern.

The best way to check whether a stored procedure exists is with

IF object_id('your procedure', 'P') IS NOT NULL

While querying system tables and INFORMATION_SCH EMA is good for admin
and maintenance stuff, I think one should be wary of doing in application
code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Robin Tucker (id************ *************@r eallyidont.com) writes:

Hmmmm. Ok, it was a user error (I was querying the wrong database). On
the other point, about being wary of querying system tables in an
application, is there anything imparticular I should be wary of? The
way I see it, there are two methods of determining if any given database
(1) supports my application and (2) supports the current version of my
application. Firstly, I could just execute the "abcd_GetVersio n" stored
procedure. I will get an exception if it fails, ergo: not a database my
software can use. Once I have established abcd_GetVersion can be
executed, I can just execute it to find the database version and
everyone is happy. The second way is to query the system tables for
objects I know are unique to my schema/object set (ie. looking for a
specific stored procedure).
The third way is to have a table which lists all components that
are installed in the database.

Admittedly, sometimes there is reason to query metadata in an app,
but the less you do it the better.

Beware that in SQL 2005 there is an important change. First of all the
current system tables becomes "compatibil ity views" that are built
on top of the new "catalog views". But as long as you query documented
columns only, this is not too much of a hassle.

More important is that in SQL 2005 the metadata is not public information
in the same way as it is in SQL 2000. In SQL 2000 if you have access to
a database, you have access to all data in the system tables. In SQL 2005
you can only see metadata for objects that you have some sort of permission
to.

Since users typically have access to stored procedures that would not
be much of a problem. But assume that you have a stored procedure that
queries systemt tables to get information about tables and columns
that the users does not have permission to. This access will fail in
SQL 2005, because ownership chaining does not apply. There is a new
permission VIEW_DEFINITION to resolve this, but it can cause some
headache before you get there.
Now the issue I have is that I'm enumerating servers in a combo box on a
form and I have a checkbox which states "only show servers hosting a
compatible database". Actually attempting to connect, run the stored
procedure and fail on exception is an amazingly slow way of doing this.
Much simpler I think, to just check the sysobjects table to see if the
given stored procedure I need is there.

So heres what I would like to do: connect to the server. If the
connection fails, then I am not a compatible server (for whatever
reason). Once connected: query something, somewhere to find out if the
server hosts a certain kind of database. Will I have enumerate the
entire list of databases and check each one individually?

Yes. And since you could find a server with thousands of databases,
this is a dead end.

If you want this, I would suggest that you have a "master" server with
known server/databases and serve those to the user. Or just store
the server/database that the user have used in the registry, and then
permit him to big any server/database he wants as an alternative, and
if he makes a bad pick just say "Sorry...".
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp