July 15, 2010 at 5:09 am
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.
July 15, 2010 at 5:49 am
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
July 15, 2010 at 5:55 am
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