6

My goal is to keep SQL Server stored procedures under source control. I also want to stop using SQL Server Management Studio and use only Visual Studio for SQL related development.

I've added a new SQL Server Database project to my solution. I have successfully imported my database schema into the new project, and all the SQL objects (tables, stored procedures) are there in their own files.

enter image description here

I know that now if I run (with F5) the .sql files then my changes will be applied to my (LocalDB). This if fine, but what if I want to very quickly run something on another machine (like a dedicated SQL Server shared by the entire team)? How can I change the connection string of the current .sql file in the Sql Server Data Tools editor?

I have the latest version of Sql Server Data Tools extension for Visual Studio 2012 (SQL Server Data Tools 11.1.31203.1). I don't know if this is related to the current version, but I cannot find anymore the Transact-SQL Editor Toolbar.

I have also tried to Right-click on the sql editor, choose Connection -> Disconnect. If I do the reverse (Connection -> Connect...) the editor directly connects automatically (probably to my LocalDB), without asking me a dialog to choose my connection.

Another strange thing I've observed, if I try to run a simple SQL query (like select * from dbo.ApplicationUser I receive the following message (even if the autocomplete works): enter image description here

Thanks.

(Note: I have the same issue with Visual Studio 2013)

Lucian
  • 3,981
  • 5
  • 30
  • 34

3 Answers3

4

Inspired by srutzky's comments, I installed the latest SSDT pack (12.0.41025). And bingo, like srutzky said there is a Change Connection option. But what's more, you can specify your Target DB by right clicking on the Project in the Solution Explorer, and going to Properties->Debug and changing the Target Connection String! If you're stuck on an older SSDT, then the below instructions will still work.

For SSDT 12.0.3- I've also been plagued by this problem! My solution is below, but it has some Pros and Cons to it...

SOLUTION

  1. I'm assuming that you are using a SQL Server Project in VS (I'm using VS2013 and SQL Server 2012).
  2. Right click on your .sql file in the Solution Explorer and view Properties.
  3. Change Build Action to None.
  4. If the file is open for editing, then close it.
  5. Reopen the file, and the T-SQL Editor should appear at the top.
  6. Click Connect, and you will connect to your (localdb).
  7. Click Disconnect.
  8. Click Connect again and the SQL Server Connection dialog should appear.
  9. Switch the connect string of '(localdb)\Whatever' to '.' (for some reason using '(localhost)' didn't work for me).

Voila, you should now be able to query against your SQL Server DBs! Rinse and repeat for every file you want this capability with... :/

PROS

  • You can finally run queries directly against your SQL Server DB
  • Your code can be organized in a nice VS solution (SSMS doesn't allow folders! :/)
  • You can (after switching Build Action setting back) Build the project

CONS

  • I'm not seeing any autocomplete/intellisense against the remote DB, although if you import your DB, then you could gain the intellisense from that
  • Requires each file to switch Build Action to None
sorrell
  • 1,801
  • 1
  • 16
  • 27
  • Just FYI, you don't need to do any of this. This is unnecessarily overly-complicated. Please see my answer for details. – Solomon Rutzky Jan 27 '15 at 16:29
  • The only options that show up under SQL->Connection for me are 1) Connect and 2) Disconnect – sorrell Jan 27 '15 at 18:36
  • Do you mean that those are the only two you can select, but there are others, OR that those are the only two items in the list? In my list I also have "Disconnect All Queries" and "Change Connection". I am using VS 2013 Community Edition. – Solomon Rutzky Jan 27 '15 at 18:44
  • Those are the only two, period. VS 2013 Ultimate with SSDT 12.0. – sorrell Jan 27 '15 at 18:48
  • 1
    I have SQL Server Data Tools 12.0.41025.0. Have you gone to "connect" to see if the other options appear? Sounds strange, I know, but I could have sworn something looked different the first time I went to that menu. – Solomon Rutzky Jan 27 '15 at 19:26
  • I just installed it moments ago and updated my answer. Thanks for the inspiration! – sorrell Jan 27 '15 at 19:29
  • D'oh! That is yet _another_ non-working item in SSDT prior to 12.0.41025!! I upgraded initially because the version that comes with VS 2013 errors when trying to build a SQLCLR project. So what part of it worked? I will update my answer as well to specify the version #. Thanks for doing that extra step. – Solomon Rutzky Jan 27 '15 at 19:40
  • Everything works now with SSDT 12.0.41025.0! I can connect to SQL Server DBs, the Intellisense works - life is good again! Thanks for providing hope and info that it shouldn't be as hard as 12.0.3* made it! – sorrell Jan 27 '15 at 19:47
  • No problem :). +1 for the info to help those who for some unreasonable reason cannot upgrade ;-) – Solomon Rutzky Jan 27 '15 at 19:48
2

This should be a fairly simple and straight-forward thing to do, that is, if you are using SSDT version 12.0.41025.0 (or newer, one would suppose):

  1. Do either:
    1. Go to the SQL menu at the top of the Visual Studio window
    2. Right-click inside of the SQL editor tab
  2. Go to Connection ->
  3. Select Change Connection

Then it will display the "Connect to Server" modal dialog window.

If you do not see the options for "Disconnect All Queries" and "Change Connection...", then you need to upgrade your SSDT via either:

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
-1

The fastest way to achieve this is create a new SQL Connection, copy and paste the code then execute.

What I do is Tools->SQL Server->New Query. Enter the database credentials (And make sure that the Database at the top is correct - I have hundreds of sp's in my master db on local :) )

Copy the source code from the editor, paste into the new query window. Then Execute (CRTL-Shift-E). You can leave this 'scratch' window open and pinned for easy access for subsequent executes.

If you want to deploy (i.e. publish) the entire database then you can setup a publish destination for each server, right click on the xml and select publish..

HeXanon
  • 514
  • 1
  • 8
  • 12
  • Thanks for your advice, but ideally I would not want to "copy - open new window - paste - run". If I follow this pattern (which for sure can be done very fast after some practice) I get no benefits for using SSDT in this case, so I rather switch back to Management Studio – Lucian Mar 26 '14 at 10:43
  • I agree in part. SSDT is great for having the source of a database in a form that is easy to add to source control and through SQLPackage.exe a great way to update remote databases. For a quick way to edit .SQL Files then yes a solution in SSMS may be easier. – HeXanon Mar 26 '14 at 11:32