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.
| Author |
Topic |
|
revdnrdy
Posting Yak Master
220 Posts |
 Posted - 2008-12-22 : 18:09:35
|
| Hello;I am curious to know where a stored procedure gets stored once its created. I ask because I created a stored procedure and now it is gone after coming back to work after the weekend. The server was not rebooted or anything but yes I did shut down my desktop pc.I tried to run it and sql reports that it cannot find it.I used the query SELECT * FROM sys.procedures and cannot see it either. No one else deleted it as I am the only admin here. It was not dropped.I didn't prepend the name of the procedure with sp_ as I don't want to consume extra resources having sql search thru the master db.Just curious what may have happened..thanks for any help.-r&r |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-22 : 22:23:28
|
| dont you have a version control mechanism where you store code of objects like procedure,trigger,...? |
 |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-23 : 12:03:52
|
| Well;I do have a folder on the server where I store revisions of queries so that's not a problem. This was necessary to become ISO certified.I know where the queries are located & can simply re-execute them so that's not the problem either. It isn't a matter of forgetting where they are.This is a small company and I am the only dba so I am certain no one has deleted my stored procedures by accident or otherwise.The sql server is on server A. I do not run mgmt studio directly from server A. I run it from another server B & simply connect to it. The queries are also stored on server B. Maybe I should move the queries to server A? I thought once you hit saved & executed the stored procedure it would stay in memory? Perhaps I am incorrect..puzzled..r&r |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2008-12-23 : 13:12:22
|
| Ok well does this make a difference?creating the procedure this way...USE mydatabaseGOIF OBJECT_ID('QWSP_UPDATE_INV') IS NOT NULL DROP PROC QWSP_UPDATE_INVGOCREATE PROCEDURE QWSP_UPDATE_INV ...rest of codeor creating it this way? (without the use keyword).CREATE PROCEDURE QWSP_UPDATE_INV ...rest of codedoes the second way create it in the temp db since a db was not specified? Perhaps that is the issue?r&r |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Stored Procedure Question