Dropping Stored Procedures

Question

Post reply

Dropping Stored Procedures

Viewing 15 posts - 16 through 30 (of 36 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Chirag (3/26/2008)

Hi

Jeff - we are still in the development phase of the project. What i meant was that daily there are some modifications to the procedures. This does not mean that all procedures are modified daily or the same set of procedures get modified daily.

Oh... ok. I was worried for you... thought someone had you modifying hardcoded dates or something.:hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Guess I'm still worried :hehe:

Are you generating scripts from the Dev database, every day, to promote to the Production database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (3/26/2008)

Guess I'm still worried :hehe:

Are you generating scripts from the Dev database, every day, to promote to the Production database?

Even if you are - shouldn't you be keeping the audit trail of the updates to said stored procedure? You know, as in Source control? I kind of sounds like you're approaching it from the opposite direction.

I do a fair amount of updates to SP's while I develop, so I keep them as .SQL scripts, which I bring in using the SSMS "project" functionality. I modify/add the scripts as needed, and then I run the script(s) to update the objects in the DB, NOT the other way around. I also check them in on a regular basis into SourceSafe, just so that I have a record of the supposedly "stable" versions of the files.

I only script from the preexisting objects if I have a problem, or had a slow day on the caffeine drip (i.e. I screwed up and didn't save one correctly). But - that's the exception, not the rule.

Having the files prebuilt makes it awfully easy to then just deploy to Prod if and when that's needed.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Even if you are - shouldn't you be keeping the audit trail of the updates to said stored procedure? You know, as in Source control? I kind of sounds like you're approaching it from the opposite direction.

No... you and I are on the same track... promoting scripts directly from Dev to Prod is almost as bad as doing a diff and promoting the diff. There's no source control, no auditing, no way back. And that's what my statement was about...

I can't believe people will spend so much $ developing and not have all the safeties in place. Just saw a post from someone else where the database and all the backups got "accidently" deleted... it was for a project that they have to deliver to a customer in a few days. They're dead meat... not even worth the fat off a pork chop. If they had a source control system, change controls, and maybe even a silly attempt at configuration management, they could at least regen the objects and the static data. Instead, we see a post that could be reworded like this... :D:P:hehe::);)

"I not only gave the developers access to the prod system, but I let them all login as the same user. I put my backups on the same disk on the same server as my MDF and LDF files. I have no "tape" or optical backups and no change control or source control system because they all take too much time to worry about. Someone "accidently" deleted everything from development and production and although there's no chance of me finding out who did it, I sure would like to know.

I "unerased" the LDF. Even though I allowed folks to continue to write things to the hard disk where the MDF and backup files were, I can't under stand why I can't unerase it or any of the backup files. And, why can't I recover the database from the LDF?

I hate SQL Server because it didn't tell me how to do my job... help me recover this so my boss doesn't take me out for a pork chop dinner even though none of this is my fault."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Naples SSC-Addicted Points: 415 More actions · Answer 5

David Naples

SSC-Addicted

Points: 415

March 26, 2008 at 8:39 am

#794077

IMHO, if you are generating change scripts every day to promote to production, it sounds like you are in need to a release scheduling process. Doing things on the fly like that opens the door to a whoole host of potential problems.

Cogiko ergo sum (I geek, therefore I am).

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (3/26/2008)

Even if you are - shouldn't you be keeping

the audit trail of the updates to said stored procedure? You know, as in Source control? It

kind of sounds like you're approaching it from the opposite direction.

No... you and I are on the same track... promoting scripts directly from Dev to Prod is

almost as bad as doing a diff and promoting the diff. There's no source control, no auditing, no way back. And that's what my statement was about...

I'm not sure why I even quoted your message earlier...My comments were directed at the OP. Totally agree with you...

As to your "rewording", first off - hehe...well done. The truly sad thing is - I could swear

this is not the first time this particular OP posted about the SAME scenario. hmm.....

I mean - doing it once is one thing. but not learning from the first time, well, that's just

sad...It might be just me, but if I keep hitting my fingers with the hammer when trying to put in a nail in an inconvenient spot, I either get the nailgun out, or I grab the nail with

some needlenose pliers. At least try a different approach or something....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Matt Miller (3/26/2008)

At least try a different approach or something....

Not blasting the OP by any means... but sometimes a "different approach" means getting someone that knows how to put the nail in without banging the fingers so you can watch once 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

ChiragNS One Orange Chip Points: 26137 More actions · Answer 8

Hi

Want to clear some things here...

All my procs are there in source control (VSS) and i manage them using solutions\projects in SSMS. Using VSS in SSMS is a drag but thats another story. In addition i have a log (excel file) where all the procs modified/deleted/created are recorded.

Also I dont move scripts from development to production. From development db --> Test DB --> Staging DB --> Production DB.

What i really wanted to know when i started this link was whether the query plans would be affected if i drop and create proceduers or i use Alter procedures. I got the answer.

But i got to know lot of other things as well.... :D.

So as they let the show go on...

"Keep Trying"

Doug S. SSCrazy Points: 2801 More actions · Answer 9

Thanks to the person that posted the SQL to show changed objects based on the modifiy date. Here's a different version that calculates the days since last change as "DaysOld" in order of the newest procs and views.



SELECT     TOP (100) PERCENT sys.objects.name, sys.objects.modify_date, sys.objects.create_date, DATEDIFF(day, sys.objects.modify_date, GETDATE())

                      AS DaysOld, sys.objects.type

FROM         sys.sql_modules INNER JOIN

                      sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND sys.objects.type IN ('P', 'V')

ORDER BY sys.objects.modify_date DESC

Cheers

Doug

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

RBarryYoung

SSC Guru

Points: 143329

March 27, 2008 at 3:59 pm

#794916

Thanks, Chirag.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

Chirag (3/26/2008)

Hi

Want to clear some things here...

What i really wanted to know when i started this link was whether the query plans would be affected if i drop and create proceduers or i use Alter procedures. I got the answer.

Your query plans are probably generated mutiple times per day, if not multiple times PER execution.

Statistics on tables can cause a procedure to recompile. Using of temp tables, etc.

http://support.microsoft.com/kb/243586

Satya-806309 Grasshopper Points: 14 More actions · Answer 12

Satya-806309

Grasshopper

Points: 14

July 7, 2008 at 3:45 am

#838743

Hai.

if anyone know how we can alter all storedprocedures exists in one database by using query in sqlservr 2000, please let me know.

Thanks in advance

Developer

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

It would probably be better if you started a new thread... when you do, you might want to explain what kind of alteration you want to make to each stored procedure. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Naples SSC-Addicted Points: 415 More actions · Answer 14

Satya (7/7/2008)

Hai.

if anyone know how we can alter all storedprocedures exists in one database by using query in sqlservr 2000, please let me know.

Thanks in advance

Developer

You can't do this directly, as the CREATE PROCEDURE or ALTER PROCEDURE statement has to be the first in the batch. However, you can query the system tables to generate the DDL statements, then copy/paste these statements into a new T-SQL window and execute them manually.

Bear in mind that in SQL 2000 you WILL need to use a cursor, and you will have to pay pretty close attention to the formatting of the text in the output window. In addition, you will need to use SET NOCOUNT OFF, or you will find the text of your stored procs will have "

1 row(s) affected

"

in the middle of them. 🙂

Hope this helps!

Cogiko ergo sum (I geek, therefore I am).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

David Naples (7/7/2008)

You can't do this directly, as the CREATE PROCEDURE or ALTER PROCEDURE statement has to be the first in the batch.

Ummm... maybe not directly, but you can create dynamic SQL with "GO" and all the good stuff... and, in SQL Server 2005, you can do it without a cursor or an explicit loop of any kind... I still want to know what the alteration would be and I still think it would be a good idea of the requester started a new post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions