Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invisible stored procedure Expand / Collapse
Author
Message
Posted Thursday, July 15, 2010 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 12, 2010 3:37 AM
Points: 2, Visits: 3
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.
Post #953002
Posted Thursday, July 15, 2010 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,903, Visits: 32,143
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #953030
Posted Thursday, July 15, 2010 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 12, 2010 3:37 AM
Points: 2, Visits: 3
Thanks!

That's exactly what the problem was. I must have created the procedure in the master db by mistake.
Post #953034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse