Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: What does this indicate ???

    SQL2008 (10.0.2757)

    I was looking for a list of stored procedures that contain the reference to a particular object.

    I ran the following query and noticed a record for an object who's name I cannot find in the display of the SQL Management Studio.

    Code:
    select	name
    from	sysobjects
    where	id in
    		(
    		select	id
    		from	syscomments
    		where	text like '%tblPayrollHdr%'
    		)
    	and not name like '%JUNK%'
    	and xtype='P'
    order
    by	name
    So, I re-did my query using the current syntax . . .

    Code:
    select	name
    from	sys.objects
    where	object_id in
    		(
    		select	object_id
    		from	sys.sql_modules
    		where	definition like '%tblPayrollHdr%'
    		)
    	and not name like '%JUNK%'
    	and type='P'
    order
    by	name
    Same result.

    Both above queries return an object with a name of "usp_GetCurrentEntryStatusTEST", but when I go to look for that stored procedure in SQL Management Studio, it's not in the database.

    I've logged-in, logged-out several times--no change. I'm using 'sa', so I should be seeing everything.

    If I try an drop the procedure, it tells me that the procedure does not exist or I do not have permission.

    Does this indicate some fundamental corruption that I should be worried-about?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What does this return?
    Code:
    SELECT *
    FROM   sys.objects
    WHERE  name = 'usp_GetCurrentEntryStatusTEST'
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    More interesting may be:
    Code:
    select type_desc
    from sys.objects
    WHERE  name = 'usp_GetCurrentEntryStatusTEST'


    EDIT: Fixed an incorrect statement....by deleting it ;-)

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Returned the following (did a bit of copying-and-pasting to put it in a readable format):

    name=usp_GetCurrentEntryStatusTEST
    object_id=1988254188
    principal_id=NULL
    schema_id=8
    parent_object_id=0
    type=P
    type_desc=SQL_STORED_PROCEDURE
    create_date=2010-02-26 10:18:34.663
    modify_date=2010-02-26 10:18:34.663
    is_ms_shipped=0
    is_published=0
    is_schema_published=0
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Schema_id = 8
    What do you get for
    Code:
    select schema_name(8)
    The procedure will be listed most likely after all the dbo. procedures, but maybe before them all.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You guys just zero'd right in on the problem . . .

    Boy, I thought I looked several times . . .

    . . . sure-enough, that SP is down at the bottom under a different schema prefix.

    Sorry to bother you guys with this . . .

    Thanks.

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Don't sweat it. I read about this problem in a book once... A really, REALLY big book!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No problem at all. You actually get extra points for having thanked the group, and not using text-speak.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •