Multiple developers modifying the same object = lost of work

ducman

Using the below steps:

1. Developer A modify stored procedure in their local db
2. Developer B modify stored procedure in their local db
3. Developer A commit and push the change (using git verbiage here)
4. Developer B pull
5. Developer B click "Deploy Project" within the SQL Change Automation window within VS2017. 

Pretty much Developer B changes from step 2 is overwritten. Is there a way to prevent this or am I doing this wrong?

Ivo_Miller

Hi @ducman,

This sounds like the expected behaviour - when you click "Deploy Project", the local development database will be updated to match the stored procedure in the project, which in this case will be the change made by Developer A.
If Developer B wants to preserve the change they made, they should import the change into the project before doing a pull. If the changes conflict, git will prompt the developer to address the conflicts.

ducman

In order to "Import and generate script", Developer B has to "Refresh". But since Developer B already pull down changes from Developer A,  the "Refresh" may blow up (depending on the type of change ... maybe a new column was added to a table and the stored procedure now query that column) which then prevent the "Import and generate script".

How do one solve that?

AdamB

The simplest solution is for Developer B to make sure to import before pulling the changes from Developer A. However, if the pull happens first you're correct there may not be a way to import. In this case, Developer B could revert to before the pull, import, then pull again. Alternatively, they can manually edit the programmable object in the project so that it contains the desired changes from both developers, and then deploy the object to the database.

ducman

That's a lot of work to prevent the lost of work.  A lot of time, you don't even know if someone else is touching the same objects that you're touching. There are now rules that everyone need to be cognizance of which I'm not a fan of. Even common scenario like this isn't even talk about in the help doc or this forum.

There's got to be a way to do an "Import" and it determine that the object you are importing conflict with the object in the project which has not been deployed yet. I don't know what "Refresh" is doing, but if we can import without a refresh and write in the "detection" code. That would help a lot.

AdamB

I'm sorry to say that at the present time we don't support importing and then resolving conflicts in the tool window. Another possible option to avoid this kind of conflict is to edit programmable objects offline directly in the project rather than on the development database. If you want to execute them whilst working in this way, you could have the programmable object file from the project open in SSMS and run it as you make changes.