I have migrated a database project from VS2008 to VS2010. While in VS08, I used to be able to right-click on a .sql or .cmd file and select a "Run" or "Run On" command from the context menu.
In VS2010, though, these menu items seem to have gone away.
I have a number of .sql and .cmd scripts that I am used to being able to run directly from the Visual Studio IDE.
Can someone point me in the right direction?
Please see this blog post: http://blogs.msdn.com/dukek/archive/2009/10/20/what-happened-to-dbp-aka-database-projects-in-visual-studio-2010.aspx
Unfortunately we were unable to add the right click on database reference/server explorer conx to the product. You can however open the sql file and click connect from the editor window to run a .sql script.
Barclay Hill Program Manager Visual Studio Data Tools (DataDude, DBPro, Database Edition, Database Projects, VS Data Tools) Please mark the responses as the answer if it resolves your question/issue. http://blogs.msdn.com/bahill
2010年4月8日 14:12That's unacceptable. Can you guys put it back please? I don't think it takes that much work.
Agree, please put back the "run" and "run on".
With "run on" we could run hundreds of stored procedures against a database connection in 2008. Very simple and fast.
What is the alternative for this functionality in vs2010?
Please put back these commands.
2010年4月22日 10:32Yes, the "Real" database projects are way to quirky and heavy compared to the lean model of .dbp. It's a huge step to go between these two. We have 10 projects, all with hundreds of sps that would have to be migrated, a months work for nothing.
I've been having a devil of time trying to understand the paradigm between the old way I did things in 2008 and the new way in 2010! Better walkthroughs would be much appreciated.
Is there a walkthrough that would show how to run code directly against a database (without build or deploy)?
What about how to get an old database project to compile in the new format?
In future versions I would rather see a simple way of running sql scripts and source controlling with an easy way of synchronizing code against a dbconnection of choice (much like you would with source control).
I used to be able to set up a connection, set the active connection and begin writing code to run on a remote database. I now cannot even seem to USE the database projects in any meaningful way. I can't get the assemblies to compile, the code to "build" or to deploy without much finagling. If I want to copy and test my code outside Visual Studio with table or database names I have to search and replace the references.
Can you point me to a place that would walk through moving database projects from VS2008 to VS2010 and have things work OOTB with functions, procedures, tables and assemblies?
Would be nice if someone answers MotherThing, because I have exactly the same questions.
Particularly: "how to run code directly against a database (without build or deploy)?"
Is this not possible? Do we have to manually connect every single SQL script we open to be able to run it? Two clicks instead of one? I don't really fancy having to build & run a compiled script every time either.
If run/run on have been removed, then why do these help files indicate otherwise?
Not very helpful.
On another note, I'm finding the layout of MSDN library to be rather unintuitive.
2010年5月4日 20:22I'm so glad they did this (not to spite all of you here, really). Now with everyone forced through the new lifecycle of developing and maintaining a database with a DBPROJ maybe Microsoft will finally reconcile the fact that as a whole this this process does not work and costs way too much to be feasible on anything but a lab project. I don't mean to take away all of the hard work the code pounders on VSTSDB have done (and there really is some cool stuff in the product) but if the principles involved in the design could just step back for a second and look at the monster they have created I think we'd be better off for it.
Heh, well - what i can't understand is why, if I just want to make one small change to a Stored Procedure, I'm expected to deploy the database project, and wait up to a minute for it to deploy and execute. And this happens every time I want to preview a change. It's really not feasible to wait this long - it would double my development time. Is there no way to speed this process up?
To clarify, all i'm trying to do is make edits to Stored Procedures (at the moment). In the old DBP project format, i would simply script the Stored Proc with a few lines at the top of the code to drop the SP if it already existed, and then recreate it. Now that i've converted my DBP to DBPROJ my stored procs are saved in the schema. I can't simply execute the proc, because if i do that it complains (no surprises) that the proc already exists. So - I have to deploy the project, and this is where the delay comes in. Am i going about this the right way? Is this how i'm supposed to be scripting/deploying/executing changes?
The problem lies in the fact that my corresponding ASP.NET project is connected to my SQL Server instance and if i want to see & test changes as I make them, i need to deploy them first, every time, before I can see them reflected in my web-based GUI. So the question is - am I missing a trick? I hope I am :)
That's absolutely terrible. I can't imagine the lost productivity this will cost my team of developers. It feels like I just flushed tens of thousands of dollars down the toilet.
Someone really thought it would be OK to force everyone to run through a full DB deploy in order update a stored procedure in a development database?
Worst. Idea. Ever.
I'll go barricade my office now. I feel a mutiny coming on.
Good news. Looks like I'll likely not be killed, but only maimed by my development staff.
You don't have to do a total deploy to update a single store procedure, method, etc., on your development database. You can click on the Connect button in the toolbar, then set the database name. (It looks like the database name is maintained as a default and doesn't have to be selected EVERY time.) Then you can right-click and select Execute SQL.
If you follow the above procedures (and if you're writing your own, re-entrant SQL instead of letting the environment write single-use CREATE scripts for you), the results of your query execution will appear in a new split window within the document you are editing.
Then, after verifying the results you can "simply" press ctrl-alt-shft-R (because apparently WordPerfect engineers designed the keyboard mappings...) to remove that split and continue editing your document.
So...great news. Instead of flushing tens of thousands of dollars away, I've likely only created a slow and steady productivity leak by upgrading to VS 2010.
If we could set a database connection as the default for a project (so that I don't have to establish the same connection over and over and over and...) and then make the results appear in an output window, rather than splitting my editing pane, we'd be most of the way there.
- 編集済み sh0knah 2010年5月5日 23:23
Welcome to my world.
We've suffered through the costly life cycle, developed our own shortcuts, and dealt with all of the dead-ends with the tool and this approach to database management solely for the benefit of leveraging this tool to as a means to produce updates to our customers for our product (i.e. using vsdbcmd.exe). Yesterday we finally got to the finish line and tried our first customer update. Can you guess what happened? You can read about it in my recent post: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/60a6a731-71b8-44fb-a6bb-efa7fcfc2592
I had the same question:
An absolute waste of time to removed such an easy and well used tool. I have common database projects that are used in mutliple website solutions. In VS 2010 you can't have 2 solutions open that refernce the same project. So firstly I've had to change all my solutions (over 20). Now I can't open a change script and run it on each of the databases that use this script. I use this feature to edit stored procedures, add data, alter tables, create tables, just about everything. Put 'Run On' back!
You have losts touch with a large part of you customer base. Forcing everybody to use a deployment process that is suited to only a few. You've dropped the ball!
Please can this be reviewed - as noted elsewhere in this forum item, this is a huge productivity hit - all I can guess is this is MS's way to try to force people down the LINQ route, even if it isnt of any immediate benefit in existing projects, or even in future pipelined projects considering many companies have code generation tools and frameworks that work just fine (and faster) than LINQ implementations.
Please please please can this be added back; it really is messy to have to run VS 2008 with VS 2010; with DB projects opening in VS 2008 and all else in VS 2010; all we need is .DBP projects back.
Hey, I liked that "we were unable to add the right click" thing :-)
To be honest, we have just switched to VS2010. No idea why didn't we wait for the service pack as usual!!!
Anyway... any estimates when DBP projects are back?
Just ran into this in my first attempt at converting one VS 2008 project (one of what will be a half dozen or so). At this point, the .dbp to .dbproj conversion tool might work and I will investigate that. If it does not, then I will be forced to spend some hours migrating these scripts manually.
I agree with the many other comments so far -- the old .dbp projects were a fast and lightweight way to store schema and data changes and deploy them to multiple database targets. It's different and works well compared to the new .dbproj way. We typically have local, dev, test and production databases and I can very quickly deploy changes to any using the VS 2008 .dbp project. Would certainly be nice to bring the .dbp project back as another project type.
2010年9月21日 14:00Absolutely infuriating. I've been in this business 30 years and I am so tired of MS moving the target on us. This feature was widely used especially in companies with dozens of database servers. What is the solution? Yeah...now we have to go digging in the bottomless-pit of wordy white papers to figure this new cluster out.
2010年11月10日 13:53I totally agree that it was a big mistake to remove these commands. It was so quick and easy to run multiple scripts against a database, now it is a huge and time consuming effort. This is a giant step backwards - PLEASE PUT THEM BACK.
I am a little confused, maybe since I never really used the old DBP projects.
Suppose there was a Run command when you right click on a .SQL file inside Solution Explorer when you have a .DBPROJ project file open, which connection context would you expect the script to get executed against? The deployment database inside the project? The personal sandbox target database when set? Something else.
The next observation would be that the scripts are not really in an executable form, they do not contain the IF NOT EXIST block like you would have inside a DBP project. So what is your expectation here?
Reason I am asking is that it seems very trivial to add a command handler which opens a file and executes the SQL script, so these are sincere questions trying to figure out of the overall behavior still makes sense, for some reason my guess is that there are more reasons why this was removed as Database Projects are an offline development environment, which have no connection state unless you compare or deploy.
Milo A. Hoffman
We have just converted to VS 2010, and were heavily dependent on the .dbp project. We manage our own schema builds, which includes thousands of stored procedures and functions - we do not want VS to manage it for us. It is inconceivable to me that MS discarded the .dbp project format, without considering the impact on developers of large commercial applications. When will the .dbp project be restored to VS? When will the "Run" and "Run On" commands be restored to the context menus? When will the ability to assign a default database to a SQL project be restored?
My solution is to just run those DBP projects in VS2008. I mean sure it'd be nice to have everything in one solution in VS2010, but rather than kvetching about wasting tens of thousands of dollars and being maimed by your developers, I think an intelligent developer can easily switch to a different IDE for a moment when we need to update some database scripts, ya think?
Don't get me wrong, I'm not happy, but it's not really the end of the world now is it?
This is an absolute disaster!!! What is wrong with you Microsoft? Who's bright idea was this to remove the menus? I think this statement "Unfortunately we were unable to add the right click on database reference/server explorer conx to the product" is absoluately hilarious - Barclay you are a true champion of your breed.
I use these context menu commands every day to release new stored procedures and updates to our databases. I'm now forced to copy/paste the SQL into SQL Management Studio to execute any changes I want.
I also have change scripts that I run on databases to upgrade schemas, etc and these files are named according to release version - i.e. 1.1.0.sql, 1.2.0.sql, 1.3.0.sql, but now it appears with the Deploy "feature" of these new VS2010 SQL Database projects, there can be only 1 PreDeployment/PostDeployment script. This doesn't make it easy to keep a history of changes and/or upgrade a database through several versions with a few clicks.
PLEASE PUT BACK THE "RUN ON" RIGHT CLICK MENU COMMAND or alternatively someone there at Microsoft can take a day to write a Visual Studio Add-In that brings these context menu commands back to any *.sql file.
I wish they would but if their track record stays the same you'll never see Microsoft re-add those commands because it doesn't fit their academic, "best"-practices approach to everything.
I'm still using VS 2008 so this may not work for you but you could try and add SSMS as an external tool (Tools | External Tools) to Visual Studio. Here's the properties I use:
Title: SMSS - Because VSTSDB Can't
Command: C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
Initial Directory: $(ItemDir)
Once setup you can add it to your toolbar -- After you've guessed which "External Tool X" it is in the list of commands in the Customize dialog.
With this setup whatever document is active will open in SSMS when you press the button or select it from the tools menu.
Unfortunately I haven't found a way to set this up so it opens successive documents in the same instance of SSMS but at least it's better than being trapped in that academic beast of VSTSDB.
I myself have been annoyed by the missing functionality in Visual Studio 2010. I have looked at various options/alternative to manage and deploy the scripts on the server, but nothing was as easy as it was in Visual Studio 2008.
Finally, I have created a tool which would allow you to deploy and run the SQL scripts as you used to do in Visual Studio 2008. This project would be made available by March 15, 2010. Its free and can be downloaded at http://sqlexecuter.codeplex.com/
Will be looking forward for your suggestions and feedback.
- 回答の候補に設定 Tushar 2011年3月15日 3:50
Your SqlExecuter really helps when you have lots of Stored Procedure scripts to execute at on time.
But please, can you add a button "Select all" ?
I made frequent use of the "Run" and "Run On" commands, and like the other people on this thread, would love to see them put into VS 2010.
2011年10月25日 0:02Not thrilled with the loss of this feature. We have several large database projects for which we can't justify migration to "the VS2010 way", and all we need is a simple "Run On" command for scripts in a project.
I got my solution migrated from VS2008 to VS2010, all good until the DB projects. All my db access is through stored procedures. I have all sp's organized in folders so they can be executed with "Run On" so dependencies were handled in order.
Now this? This is COMPLETELY INCOMPETENT.
No default connection to a database? COMPLETELY INCOMPETENT.
This seems like even the project manager has never dealt with anything but a toy database.
I can personally take the hit for this purchase of VS2010, right into the trash. There is no way I can recommend VS2010 to anyone. It will be a long time until I trust the Visual Studio development team again. I was a Visual Studio disciple. I am very sad about this breach. At least I have VS2008 to fall back on.
Here it is March 2012, no fix in sight, no service pack promised, just silence and excuses from the VS Team. You are fired.
CrCr: You've got my vote. Unfortunately the database dev guys at Microsoft have chugged their own Kool-Aid. You wont see any "fix" in sight; just slight-of-hand. They fully believe the database developer is untrustworthy and therefore all of their changes must be vetted by a full build & deploy. If you watch any of their videos about Juneau they won't even mention the words "build & deploy"; they just refer to it as pressing "F5" because they know fully well how painfully slow build & deploy is on any sizeable schema.
One of the videos talks about how your can test your code from source (which of course you'd hope was a demo of editing and testing a sproc without build & deploy) but of course they narrow the scope to "testing" a select statement in a view by running the statement against the database. To test edits to a single sproc you need to:
- Perform a full build & deploy of your model to the db OR
- Edit the sproc in the model, temporarily change the source code's CREATE statement to ALTER, run it in by hand, and remember to change it back to CREATE before you check it in OR
- Make changes directly in the db and schema compare them back into the model (there can be merge issues with this approach)
They could make #2 & #3 easier for us but it's clear they'd rather have us use #1 even though doing a full build and deploy doesn't vet everything.
I agree with your sentiments "This seems like even the project manager has never dealt with anything but a toy database."