March 2, 2008 at 5:02 am
Hi,
I store databases in the form of scripts(Generate Scripts), If i have Stored procedure which was written at beggining and I wants to change that also I need to keep tack of old SP. This is my requirement
I am following this prodedure till now
as database size increases cant search SP so if there any changes to be done in SP will include "ALter SP...." in the last whenever i need to earlier SP will search for the from downwords...
please anybody help me in keeping track on it in smarter way??
March 2, 2008 at 8:13 am
You may want to check database triggers.
At SSC you can find examples and some articles regarding db audit, ...
e.g. http://qa.sqlservercentral.com/articles/Auditing/62126/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 2, 2008 at 8:32 am
You can use triggers to collect data about altered DDL, as ALZDBA suggested.
My question to you is do you have all of your scripts in some sort of source control? This is the best way to determine what the version of the script is, what changed, and who changed it.
March 2, 2008 at 8:50 am
Hi,
Yes data is stored in TFS, Thanks a lot for suggesion.
Will get back to you if i have any doubts in this... 🙂
March 3, 2008 at 6:42 am
If I understand the question, you could add an extended property to identify the current version of the procedure.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 5, 2008 at 12:16 am
You could also so something simple like script out all your procs/views/etc. on a regular basis. We do this daily into a history table and have a running version history of all changed objects going back in time. Kind of like a poor-man's version control without any "who did it" tracking... but at least you can grab the previous day's version of an object if it was created earlier than today. Quite lightweight and no need for any expensive or cumbersome 3rd party products.
Regards,
Jacob
March 5, 2008 at 12:16 pm
It's certainly not automatic, but my first inclination is to ask...
1. You allow folks to change production stored procedures without DBA approval?
2. Are there no documentation standards? A simple note in the revision history section of a decent header tells all.
3. Is there no source control on your procs? VSS or SubVersion?
--Jeff Moden
March 5, 2008 at 12:23 pm
this is also not automated - but running a "generate scripts" task once a week would give you a quick benchmark. You coud use something freeware like WinDIFF to highlight all differences.
But still - that's like using your foot to look for land mines... Very messy, and you only know once the damage has been done:)
----------------------------------------------------------------------------------
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?
March 5, 2008 at 12:24 pm
Hi,
Data is in TFS which is path based Source control from microsoft, As i am administrator for this scripts I can alter scripts.
But my requirement is that i want to track change history in one place so that i can access all versions of that code.
March 5, 2008 at 12:42 pm
Well - since you have a source control/version control solution - it's now time to "incentivize" using it. Meaning - lock your developers out of prod. The only way out/on to Production is to get the new version published into TFS, and have it reviewed, and publish it to production from TFS.
That's the only way you will get your change history. TFS can't track versions of stuff that aren't published through it: it's the same as asking you to remember a conversation you didn't have.
----------------------------------------------------------------------------------
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?
March 5, 2008 at 12:51 pm
Thanks a lot will use TFS from now. 🙂
March 5, 2008 at 1:30 pm
Sometimes, I feel pretty stupid... I'm still coming up to speed on 2005 (installed at home in December)... what in the Dickens is "TFS"?
--Jeff Moden
March 5, 2008 at 1:37 pm
TFS = Visual Studio Team Foundation Server. It's a bear of a product: version control/project management/project documentation all rolled into one. And all that for a bear of a price tag, too:)
http://msdn2.microsoft.com/en-us/teamsystem/aa718934.aspx
It's like VSS on steroids + Application stress tester on steroid + project on steroids = ludicrous per user CAL price. But hey - it IS rather cool though....:)
----------------------------------------------------------------------------------
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?
March 5, 2008 at 1:51 pm
Thanks, Matt. Guess I won't be buying that for my home machine in the very near future... 😀
--Jeff Moden
March 5, 2008 at 1:56 pm
Why buy when you can evaluate?
You'll need a bigger machine though....:) It takes a rather massive chunk out of your machine (it's essentially a customized sharepoint install running on top of a local SQL Server install, all using IIS to serve it up. And it likes to take over your default IIS install, so it doesn't play incredibly well with other installed products like reporting services).....
Oh yeah - it's RAM heavy, processor heavy, and process heavy....
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply