db7738@nospam.com <db7738@nospam.com> wrote in message news:<40291e34$0$198$75868355@news.frii.net>...[color=blue]
> I apologize. I pieced together my SQL and the result before. I'll try
> to handle it consistently here. In database 'plibrary' I'm trying to
> get some information about my tables.
>
> use plibrary
> go
> sp_help
>
> returns a whole list of object, among them:
>
> PARCEL property user table
>
>
> sp_help parcel
>
> returns:
>
> Server: Msg 15009, Level 16, State 1, Procedure sp_help, Line 71
> The object 'parcel' does not exist in database 'plibrary'.
>
>
> sp_help property.parcel
>
> returns:
>
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
>
> I don't have this problem with tables in 'Northwind' database, but I do
> have the same problem in my 'raster' database.
>
> I have recently restored the 'plibrary' database and had to re-run
> sp_defaultdb for a bunch of users because the dbid for database
> 'plibrary' had changed. I'm wondering if there are other places where
> the dbid is stored that may also need to be corrected for the new dbid
> maybe?
>
>
> Okay, onto the next thing.
>
> select * from sysobjects where name = 'PARCEL'
>
> returns (uid = 36):
>
> PARCEL 1688393084 U 36 3 1619460099 384 0 0 2004-01-29
> 10:59:38.857 0 384 0 U 1 115 0 2004-01-29
> 10:59:38.857 0 353436333 337436276 353436333 0 0 0
>
>
> select object_name(36)
>
> returns: NULL
>
> Now I see that if I connect as the table owner 'property' I do get a
> result from sp_help, but shouldn't I be able to get that same
> information from sp_help if I'm connected as 'sa' or some other user
> with read privilege (the table is read-able by public)?
>
> Thanks so much for your help.
>
> D Bull
> GIS Programmer/DBA
>
> *** Sent via Developersdex
http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]
sp_help doesn't work because you aren't the owner of the object, as
you've acknowledged. The fact that "public" has read access doesn't
change this - that only applies to the data in the table - even if
you're connected as "sa".
The object_name() check should have been given the objectid (you
appear to have passed the uid - did you really mean this?). I don't
think it's important any more as the answer appears to be clear - it's
all down to the table ownership.
sp_help property.parcel won't improve things because
a) it's syntactically incorrect (you need to be in the local
database), and
b) there is no such database as "property" :-)
The fact that you've restored this database is irrelevent; the tables
have no concept of the database ID, since they're entirely
self-contained within it.
The only other issue I can think of which may be affected by dbid's
changing is if you have any VIEWS which include cross-database joins.
Compiled view definitions are stored in syscomments (like stored
procedure code), and reference objects via their dbid's. If a dbid
for an object in a view changes, then the view will fail to work, or
(worse) return data from another database (although that's a little
unlikely). You'd need to drop and recreate the view definition in
this case so that all table names are re-mapped to the correct
dbid/object_id. NB - this is only necessary if the view references
objects in another database, and if it is this other database which
has had a change of dbid.
