Post reply

Same script...different places.

  • 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?

  • 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!

  • 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!

  • 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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply