Stored Procedure being saved in System Stored Procedures

Stored Procedure being saved in System Stored Procedures

• fristi

  No, that is why I suggested he post his code. Creating a procedure in the master database with a name of sp_ will cause it to be usable from all databases, but not set the is_ms_shipped bit. I

  set nocount on
  go
  use master
  go
  create procedure sp_test
  as
  select name from sys.tables
  go
  use tempdb
  go
  sp_test
  go
  use master
  go
  select type_desc, is_ms_shipped
  from sys.procedures where name = 'sp_test'
  go

  Returns:

  name
  --------------------------------------------
  spt_fallback_db
  spt_fallback_dev
  spt_fallback_usg
  spt_monitor
  spt_values
  MSreplication_options

  type_desc is_ms_shipped
  ------------------------- -------------
  SQL_STORED_PROCEDURE 0

  
  
  

• HolySmokes

  use [master];

  CREATE Proc dbo.usp_Test
  As
  Begin
  select 'Im a sysproc'
  End;

  go

  --select is_ms_shipped from schema, its returns a 0

  
  

• DimitrisTsangaris

  name	object_id	principal_id	schema_id	parent_object_id	type	type_desc	create_date	modify_date	is_ms_shipped	is_published	is_schema_published
  p_BackupDatabase_NE	615673241	NULL	1	0	P	SQL_STORED_PROCEDURE	12:55.9	12:55.9	1	0	0

  The dates didn't come across in that correctly because I pasted it to Excel without formatting it.  But you can see that is_ms_shipped = 1.

  
  

• Milan Dimitrijevic

  Hi,

  Can you please send the results of

  select * from master.sys.all_objects where name in ('sp_your_proc1', 'sp_your_proc2', ...)

  Thanks

  
  
  

• chessmaster

  We completely uninstalled the previous version (SQL 7.0) and installed SQL Server 2005. That effectively wiped out all the stored procs we had in our master database. I ran the code that appears in an earlier post after the installation.
  

• allan.taruc

  Wow, a great big "duh" for me today, eh

  I created your procedure, and then ran it, but it is not a system for me. This looks like a bug of some sort and I don't see anything on the Feedback Center, so I will leave it to Clifford. Sorry for all of the confusion :)

  
  
  

• decyclone

  I would imagine the upgrade process interpreted those procs as system ones.

  You cannot make direct updates to catalog tables even if you start the db in single user mode -m and drill down to sys.sysschobjs.

  There are several differences between sys and user procs:

  1. Referencing, you must fully-qualify USPs including those master
  2. Editing, you cannot edit a SysProc
  3. Storage, sysProcs are in the Resource DB, while USPs are per UserDB. sysProcs appear in all sys schema of every db USPs do not

  Get with the Microsoft guy here, thats my advice. Setup issue I would imagine

  And no, I'm not a MSMVP (but I am flying there for an IView, does that count),

  Hope this helps,

  Derek

  
  

• pezi179

  I think (and I may be wrong) all the master stored procs are stored as system stored procs (i.e. I don't think you can create non-system stored procedures in master):
  
  http://msdn2.microsoft.com/en-us/library/ms187837.aspx
  
  To any MVP's out there, am I correct
  
  HTH
  
  

• skmichi

  The system procs in master in Shiloh were migrated to the so-called "resource database" in Yukon. Search the BOL for "mssqlsystemresource" and/or "resource database". Procedures in msqlsystemresource database logically appear in the 'sys' schema of every database (including master).

  You can continue to create your own (non-system) procs in master in Yukon. You should avoid using the 'sp_' name prefix because of potential name conflicts with future MSFT SQL releases. We always search the 'sys' schema first, and so the name resolution algorithm will bind to the MSFT version of sys.sp_xyz before your own master.dbo.sp_xyz.

  Making your own master.dbo procs "look like" system-supplied procedures by setting is_ms_shipped = 1 is undocumented and not supported and is not guaranteed to upgrade in the next release.

  Another 'trick' to avoid in Shiloh (SQL 2000) is creating your own functions in the system_function_schema. It no longer exists in Yukon (SQL 2005) and your code won't upgrade.

  If you want your code to upgrade cleanly to the next release, then avoid all these undocumented 'tips-n-tricks.' If it isn't clearly documented in the BOL with a reference page devoted to it -- don't use it, regardless of which book or magazine blog you saw it on.

  The original poster might've uncovered some kind of upgrade bug, but it's hard to say without more information.

  Thanks

  
  
  

• Gopinath Ramamoorthy

  Interesting. Can you post the code you used to create the object That might shed some light...hopefully :)

  
  
  

• Dan Devine

  The stored procedure just runs a SQL LiteSpeed backup for all the databases on the server.

  Even odder, when the same code was run on other servers, it placed the stored proc in "Stored Procedures", not "System Stored Procedures". The stored proc only got into "System Stored Procedures" on 1 of 7 servers.

• jef.ptc

  <<The reason your procedure is marked as MS-Shipped is probably because catalog update flag turned on, or server started in matainence mode when you create the procedure. >>

  I have deleted the procedure and rerun the code, but it always comes back on this server as a System Stored Procedure.

  Where can I check if the catalog update flag is on

  
  

• curt1

  Was this upgraded Or did you create it If so, do you have the code you use to create the procedure

  
  
  

• JThiloR

  The reason your procedure is marked as MS-Shipped is probably because catalog update flag turned on, or server started in matainence mode when you create the procedure.

  This behavior will be removed in future releases.

  Thanks

  Andrew
  [MSFT]

  
  
  
• Stored Procedure being saved in System Stored Procedures