Same script...different places.

• graymer

  SSC Eights!

  Points: 975

  More actions

  January 27, 2012 at 10:42 am

  #391423

  I've got a Prod system and Stage system I've fired off the same script on both systems. The prod box created the stored procs as I've always seen it. The Stage system created the stored procs one level deeper into the System Stored Procedures folder, where it's causing problems with the execution. I'm not sure why it's doing this...any thoughts?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 27, 2012 at 10:53 am

  #1439535

  is this a SQL 2000 machine, by chance?

  in SQL 2000, it was possible to set a flag accidentally that set all your procs you create in that session as system procs.

  in 2005 and above, it was changed to a command-per-object to make them system procedures.

  --SQL2000:

  --Turn system object marking on

  EXEC master.dbo.sp_MS_upd_sysobj_category 1

  CREATE PROCEDURE ....

  GO

  --keep adding proedures.....

  --settings stays on till window closed or explicitly disabled:

  --Turn system object marking off

  EXEC master.dbo.sp_MS_upd_sysobj_category 0

  --sql 2005:

  --EXECUTE sp_ms_marksystemobject 'pr_MyStoredProc'

  the only way to turn them back to regualr procedures isntead of system is to drop and recreate...you can never "unmark" something once it's marked as a system proc.

  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!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 27, 2012 at 10:54 am

  #1439536

  just checked, and sp_ms_marksystemobject is available on my SQL2008 machine, but sp_MS_upd_sysobj_category is not.

  maybe a system that was upgraded in place might still have it?

  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!

• graymer

  SSC Eights!

  Points: 975

  More actions

  January 27, 2012 at 11:22 am

  #1439544

  I was checking out sp_MS_upd_sysobj_category when I found and MSDN article http://social.msdn.microsoft.com/Forums/en/transactsql/thread/204355ae-b237-43d3-ab71-5e04f2f641aa and it pointed me to

  use master

  go

  exec sp_configure 'allow updates' , 0

  go

  reconfigure with override

  go

  Recently took over these servers didn't know they left the 'allow updates' set. I didn't think one thing had to do with the other for this but good to know. Good times stepping into other peoples stuff. I ran the above and reran the script I'm good now.

  Thanks for the help Lowell!