I am currently modifying a web application to use a MS SQL 2008 database with will be located in the app_data folder and auto-attached. Everything is fine and I can connect to the database and query it from within the application and also Visual Studio.
Although I need to also create/update some triggers and stored procedures, which I can not do - every time I choose the 'Add new stored procedure' from the right click menu in Visual Studio's Server Explorer menu, it creates a new template but I am unable to execute it, as there are not options enabled on the 'Query Designer' tool bar.
Maybe I am doing something completely wrong, but surely it should be just a case of creating my stored procedure and then clicking an execute button?
If you have never used Server Explorer for any database schema editing in Visual Studio and have previously only used SQL Server Management Studio, all I can say is that it is very unintuitive!
From within SQL Server Management studio, you create the procedure using the usual 'CREATE PROCEDURE' code and then execute it, if you happen to click the save button you are able to save a local copy as a .sql file, but doing so will not change anything within the database.
In Visual Studio's Server Explorer however, there is no way to execute any SQL, you can not execute any 'CREATE PROCEDURE', 'ALTER TRIGGER', etc. If you want to make changes to an object you have to click save instead - which is certainly not what you would do from within SQL Management Studio!
Microsoft certainly don't make simple tasks easy!