Invisible stored procedure

  • Hi,

    I have very weird problem with a SQL Server 2005 database.

    I've created a stored procedure. It executes fine. The problem is I don't see it in Management Studio.

    sp_helptext 'PROC_NAME' returns

    Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

    The object 'PROC_NAMEt' does not exist in database 'XXXXX' or is invalid for this operation.

    Select * From sysobjects Where name like '%PROC_NAME%'

    doesn't return anything.

    I've never changed any permissions on it and I'm always using the same user to access the db so I doubt its a permission problems. The procedure is very unlikely to be cerated as encrypted (I don't recall dong so ever).

    Did anyone had similar issue?

    Any other way to display the procedure code?

    Any comments would be appreciated.

  • the devil is in the details...

    what is the real name of the proc?

    for example, you know that anything that starts with "sp_" is assumed to be in the master database...maybe THAT's where the proc is. you can call a proc from master from within any database.

    so if you create the procedure sp_find in the MASTER database, because it starts with sp_, if you are in any other database, exec sp_find 'example' will work...so if you try to do sp_helptext sp_find in the SandBox database, you would see the same error, because it doesn't exist in SandBox, but in master.

    CREATE procedure [dbo].[sp_find]

    @findcolumn varchar(50)

    AS

    BEGIN

    SET NOCOUNT ON

    --print object_name(@@PROCID)

    SELECT

    TableFound,

    ColumnFound

    FROM

    (

    SELECT

    1 AS SortOrder,

    sysobjects.name AS TableFound,

    '' AS ColumnFound

    FROM sysobjects

    WHERE sysobjects.xtype IN('U' ,'V')

    AND sysobjects.name LIKE '%' + @findcolumn + '%'

    UNION ALL

    SELECT

    2 AS SortOrder,

    sysobjects.name AS TableFound,

    syscolumns.name AS ColumnFound

    FROM sysobjects

    INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

    WHERE sysobjects.xtype IN('U' ,'V')

    AND syscolumns.name like '%' + @findcolumn + '%'

    ) X

    ORDER BY

    SortOrder,

    TableFound,

    ColumnFound

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks!

    That's exactly what the problem was. I must have created the procedure in the master db by mistake.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply