November 20, 2008 at 6:16 am
Hi all,
I have more than one application and these applications use some shared Stored Procedures. Any application can modify the shared stored procedure.
If for an example, lets consider two application App1 & App2 and a stored procedure SP1. At first, App1 team modifies the Stored Procedure SP1 and again the SP was modified by App2 team. If after deploying the latest SP1 modified by App2, if again the SP modified by App1 is run, then it would get overwritten by old version of SP.
My query is, whether there is any way we could maintain version for a Stored Procedure. That is, whether we could avoid running the old SP over the latest SP? Or is there a way to avoid this.
November 20, 2008 at 6:29 am
Or is there a way to avoid this.
Sure. Developers shouldn't change stored procedures. It is a DBA responsibility to accomodate multiple requests from developers.
November 20, 2008 at 6:42 am
You can use source control such as Subversion to store all your SP code and use this to keep track of changes
November 20, 2008 at 6:57 am
H-m-m...
Are you sure? I was under imression that Subversion does not have a direct integration with SQL server...
November 20, 2008 at 7:02 am
Glen (11/20/2008)
H-m-m...Are you sure? I was under imression that Subversion does not have a direct integration with SQL server...
Yeah I am sure, have been doing it here for the last six months. SubVersion does not directly
integrate with SQL server, the only product I know that does is VSS, we use SQL compare from RedGate to produce the scripts that we check into subversion.
But you can do it without this product through a bit of clever t-sql
November 20, 2008 at 7:15 am
... we use SQL compare from RedGate to produce the scripts that we check into subversion.
Sorry for bringing an initial question to a slightly different direction, but would you please explain in a little more details?
Besides the fact, that the process which you are describing sounds semimanual, I am wondering about details of branching and merging the code in Subversion after a new version of stored procedure is saved?
November 20, 2008 at 7:29 am
There is some manual work involved and it is not 100% ideal but i am getting better at using it. I work within an open-source development team and they all use SubVersion so i had no choice but to use it.
We Tortoise SVN to handle the changes, this article has some good information on setting this up.
http://www.sqlservercentral.com/articles/sql+tools/61769/
and there is a lot of information in the red-gate forums..
November 20, 2008 at 7:34 am
Thank you, Steve.
Interesting article.
I am using ApexSQL Diff in conjunction with VSS ...
November 20, 2008 at 11:09 pm
noordeenshah.nf (11/20/2008)
Hi all,I have more than one application and these applications use some shared Stored Procedures. Any application can modify the shared stored procedure.
If for an example, lets consider two application App1 & App2 and a stored procedure SP1. At first, App1 team modifies the Stored Procedure SP1 and again the SP was modified by App2 team. If after deploying the latest SP1 modified by App2, if again the SP modified by App1 is run, then it would get overwritten by old version of SP.
My query is, whether there is any way we could maintain version for a Stored Procedure. That is, whether we could avoid running the old SP over the latest SP? Or is there a way to avoid this.
the correct procedure is, the developer should check out the required procedure , so that it will showing that the procedure is in use. and when he completes his changes, the other developer(s) can follow the same path
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 21, 2008 at 12:05 am
Hi
Iam working on a proj with a very similar scenario.
First Iam the DBA and i change the procs and have the final call on them. This makes it easier to control.
If a proc is to be changed for one app then either the other apps follow the change or i write a new proc for the same.
This depends on the kind of change and what impact it would have on the other apps and also the all round development effor that would be required. For Ex: Say iam adding a couple of new parameters to the proc then the applications also have to change their code, compile and deploy to production. So there is clearly no one best method for this.
I use VSS integrated to SSMS but this for safe storage rather than versioning.
Edit- I use SQL 2005 so the VSS integration to SSMS does not apply in this case.
"Keep Trying"
November 21, 2008 at 12:15 am
Chirag (11/21/2008)
HiIam working on a proj with a very similar scenario.
First Iam the DBA and i change the procs and have the final call on them. This makes it easier to control.
If a proc is to be changed for one app then either the other apps follow the change or i write a new proc for the same.
This depends on the kind of change and what impact it would have on the other apps and also the all round development effor that would be required. For Ex: Say iam adding a couple of new parameters to the proc then the applications also have to change their code, compile and deploy to production. So there is clearly no one best method for this.
I use VSS integrated to SSMS but this for safe storage rather than versioning.
Edit- I use SQL 2005 so the VSS integration to SSMS does not apply in this case.
see i dont know about your current implementation. but the architecture i m following is like that
we haver 4 layers
1. UI
2. Business logics
3. data access
4. stored procedure
here UI is only for the data in/out and nothing more, business logic layer does all the calculateions , and data access layer passes the data to the procedure. if the case is such you need to onlly compile the data access layer. and in case of new method creation in the DAL, your proc should return more information in the form of dataset or xml, and the proc will receive its required information and hence you can use one proc for may tasks.
and regarding VSS, yes it is a useful tool
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 21, 2008 at 12:32 am
In our company we use:
- VSS to maintain the differents versiones of the sp setted up in the production environment.
- A develop environment, in wich developers make the changes.
- A process of test and check before the pass to the production environment
- And users, cannot never modify anything in the production environment.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply