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
Post a Comment