| Author |
Topic |
|
readysetstop
Posting Yak Master
123 Posts |
 Posted - 2006-11-08 : 11:28:37
|
| Thought I should post in the newbie forum for a while, instead. :-)I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?Your thoughts, oh SQL gurus? |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-08 : 11:31:51
|
| See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73313 |
 |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-08 : 11:55:15
|
| Good info. Thanks.-D. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-08 : 13:16:52
|
| Brett & Kristen:Points taken. I'll come up with something else for naming, and start re-evaluating the procs we're using. I'm thinking I need a separate database (or maybe server) for some of this admin stuff.Kristen, I'm actually changing master.dbo.sp_add_operator. (Yes, I know it's a bad idea. Working on a replacement.) :-)-D. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-08 : 13:19:21
|
| master.dbo.sp_add_operator is fine - you will be able to use sp_add_operator from any database (assuming that's what you want!)Just to clarify (doubt its needed, but!):USE MyDatabaseEXEC sp_add_operatorwill use the version of sp_add_operator in MASTER, regardless of whether a version also exists in MyDatabase. |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-08 : 13:41:42
|
| OK, so maybe not so bad to alter master.dbo.sp_add_operator, then, as long as that is the one you're intending to use. Gotcha. It would be the only one I use anyway.I do have a couple user defined functions that I have added to the master db. I wanted them to be available for use from every DB. Stuff like input file status checks, and the cdo_sysmail SProcs. I'm assuming this is OK...?-D. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2006-11-08 : 14:38:30
|
| Apparently you can. :-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-08 : 17:10:23
|
You can roll your own, of course, based on a pre-existing system one:USE mastersp_helptext 'sp_add_operator'modify as appropriate, change the name to [sp_MY_add_operator], and Bobs-your-Uncle!I didn't twig that this was a system Sproc first-up, sorry about that - I could have been a bit quicker out of the block. Well caught Brett ... but time to get back to Blogging "Early days but long nights with 2K5 "Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Thoughts on ALTER vs. DROP & CREATE.