sql - Stored Procedure Does and Does Not Exist -


i have sql server 2008 r2 database stored procedure may or may not exist.

if run sp_help spthing, returns row if existed (name: spthing, owner: dbo, type: stored procedure).

if try create new stored procedure same name, error "there object named 'spthing' in database".

but

if run select object_id('dbo.spthing'), returns null.

if run exec spthing, says "could not find stored procedure 'spthing'".

if run drop spthing, says "cannot drop procedure 'spthing', because not exist or not have permission".

if run select * sys.objects name = 'spthing', no row.

if run select * information_schema.routines routine_name = 'spthing', no row.

all these run same connection, administrative user.

does stored procedure exist?

edit:

the sp trivial select, along lines of:

create procedure spthing     @param int begin      select strthing     tblthing     lngparam = @param;  end 

all works fine -

if object_id ('dbo.spthing') not null      drop procedure dbo.spthing go  select object_id('dbo.spthing') go  create procedure dbo.spthing      @param int  begin      select strthing     dbo.tblthing     lngparam = @param  end go  select object_id('dbo.spthing') 

output -

----------- null (1 row(s) affected)  ----------- 664283184 (1 row(s) affected) 

Comments