Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invisible stored procedure


Invisible stored procedure

Author
Message
kroz93
kroz93
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38926
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!

kroz93
kroz93
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
Thanks!

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search