CODEWITHSUNDEEP
sql-servervisual-studiosql-server-data-tools
carlosm
carlosm

Reputation: 755

Altering stored procedure on visual studio and generate schema compare

We are using SQL Server 2014 with Visual Studio 2015, we have a database project and a database.

Some programmers like to update procedures on visual studio and some others from management studio.

We have an issue, if you create the db project from database, all files created, for example stored procedures are generated with CREATE in tfs, same way the sp is saved on the database. When I do schema compare, all match. If someone use visual studio for altering one sp, he needs to change the file putting ALTER, this will allow developer to update the sp in the db from visual studio.

Now if I do schema compare again from db to tfs, I will have a new object to add in tfs because in the database is with CREATE and in tfs is with ALTER. How can I solve this issue?

Upvotes: 1

Views: 5710

Answers (2)

David Atkinson
David Atkinson

Reputation: 5899

"If someone use visual studio for altering one sp, he needs to change the file putting ALTER, this will allow developer to update the sp in the db from visual studio."

This is where you're going wrong. You mustn't change the files in the SSDT database project from CREATE to ALTER. If you want to alter an object in a connected manner (ie, on the database) you have a couple of options:

1) Open a query window (either in SSMS or from the Server Object Explorer) and execute an ALTER (note that this ALTER just gets executed and not saved in the project). You then run Schema Compare between the database and the project and apply changes to the project. Note that this isn't the supported/preferred workflow of SSDT. Instead it is designed to be used offline by changing the CREATE files, but this model won't work in SSMS.

2) You can consider ReadyRoll, which is developed at Redgate where I work. This is a database project in Visual Studio but it supports the connected workflow, which means that developers can make changes to a dev DB in either VS or SSMS and these changes can be imported back to the database project in a mouse click. This option has the advantage of being able to use the same development methodology in VS and SSMS alike.

Upvotes: 1

Gavin Campbell
Gavin Campbell

Reputation: 826

If someone use visual studio for altering one sp

This is the part that doesn't really fit with the "standard" SSDT workflow. SSDT generally assumes you are going to build a project (with or without TFS) and then update the whole database at once using "publish".

If you need to deploy single objects from Visual Studio, there are a couple of options such as "Quick Deploy" from https://agilesql.club/Projects/SSDT-Dev-Pack. This extension will let you deploy a single object (of some types) without changing the CREATE to an ALTER but bear in mind that using tools like this for routine deployment gives up many of the benefits (validation etc) of using SSDT in the first place.

Upvotes: 1

Related Questions