DB Pro and compiling SPs

Question

In the simple database projects, I used to be able to right click a stored procedure and run it against a database, and included in the script, were the drop, create, and grant permissions.  With DB Pro, this is no longer possible (or at least it yells at me if I have anything other than a create proc statement).  Thus, in order to make a change to a proc and then test it, I have to manually drop the proc from the database, then run the create proc from db pro, and then manually run the grant to the proc.  This is a big pain for simple (and frequent) changes to SPs.  Is there an easier way to do this?  My development process is to write unit tests and business/data layer code, change the proc, then run the unit test to verify it works.  I'd like to be able to "deploy all procs" which would drop all of them and run them again and apply the necessary permissions WITHOUT dropping and recreating the tables, views, etc.  Is there a way to do this?

Answer 1

Hi Jiffy

Some clarification with you.

What version and edition of Visual Studio do you used to use and what do you use at present?

What the exact steps you were doing before getting the message from VS and what did the message say?

Thanks.

Figo Fei

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

DB Pro will automatically update your target database for you. Simply modify your sprocs, tables, permissions, whatever and run Deploy. Note that you need to define your permissions in the .permissions XML file. So, change your process to: 0) deploy your solution, 1) implement your DAL code, 2) run your DAL unit test, 3) fix your sproc, 4) rinse and repeat. DB Pro figures out the correct ALTER, DROP, CREATE script to run.

If you want to explicitly deploy all of your sprocs to a database you can use Schema Compare. Set the source as your project and the target as your database. Run the comparison and clear any "updates" that you don't want. Do a Write Updates and the target will have all your updated sprocs.

If you really want to do the same thing in DB Pro that you were doing using the old DBP projects, you can do that by changing the Build Action for the .sql file to "Not In Build." You can do that by selecting the file in Soltion Explorer and opening the property window. You will no longer get the errors you are seeing. Of course, you also won't get any of the features that DB Pro provides. Schema Compare, for example won't know about your code. Yoru just using project system as a bucket to store .sql files.

---

Duke Kamstra - Program Manager - VSTS Database Edition (Data Dude, DBPro)