Copying system stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Copying system stored proc

Author

Topic

tcbrown24
Starting Member

8 Posts

Posted - 2007-01-22 : 14:50:40

I am trying to copy sys.sp_spaceused so that I can change the output data. I run the original sp and it works fine. I run the copy of the sp, with no coding changes, and it fails with the error message. the object 'x' does not exist in database 'y' or is invalid for this operation. I am running both sps as the same user. If I change the code so that the table name is not passed in I can run it in a query window just fine.
Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 15:40:13

well, we'll need to see how u modified it to spot the problem...

tcbrown24
Starting Member

8 Posts

Posted - 2007-01-22 : 15:54:15

I didn't change the procedure I just made a copy of the same procedure with a different name to test run prior to changing the code. I used sp_helptext 'sp_spaceused' to get the code to create the procedure and change the name in the create procedure statement.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-22 : 16:00:39

did u create new copy in master?

if yes, then try script object as create to new window.

may have scoping issues or naming issues which will be resolved this way (by adding brackets around object names and owners).

if no, well u need to create in master or prefix all object names with dbname and owner

tcbrown24
Starting Member

8 Posts

Posted - 2007-01-23 : 08:24:35

yes I created it in master.
Tried scripting to window same results.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-23 : 10:58:36

problem is probably this line:

SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

you'll need to modify to have it resolve to current db instead of master. might need sp to be owned by sys schema

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-23 : 11:28:46

you have to mark it as system object:
http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx

Go with the flow & have fun! Else fight the flow