Geek Answers Handbook

How to change connection in Sql Server Data Tools Editor in Visual Studio

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 added a new SQL Server database project to my solution. I have successfully imported my database schema into a new project, and all the SQL objects (tables, stored procedures) are in their own files.

enter image description here

I know that now, if I run (with F5) .sql files, my changes will be applied to mine (LocalDB) . This is fine, but what if I want to run something very quickly on another machine (for example, a dedicated SQL Server common to the whole team)? How to change the connection string of the current .sql file in the Sql server data tool editor ?

I have the latest version of the Sql Server Data Tools extension for Visual Studio 2012 (SQL Server Data Tools 11.1.31203.1). I do not know if this is related to the current version, but I can no longer find the Transact-SQL editor toolbar.

I also tried right-clicking on the sql editor, select Connection -> Disconnect. If I do the opposite (Connection → Connect ...), the editor connects directly (possibly to my LocalDB) without asking me how to choose my connection.

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

Thanks.

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

+6
source share
3 answers

This should be a fairly simple and straightforward way to do this, that is, if you are using SSDT version 12.0.41025.0 (or a newer one, one would assume):

  • Do either:
    • Go to the SQL menu at the top of the Visual Studio window
    • Right-click inside the SQL Editor tab.
  • Go to Connection ->
  • Choose Change Connection

Then the Connect to Server dialog box appears.

If you don’t see the “Disable all requests” and “Change connection ...” options, you need to update your SSDT with:

+2
source

Inspired by srutzky's comments, I installed the latest SSDT package (12.0.41025). And bingo, like srutzky, says there is a Change Connection option. But what else, you can specify your target database by right-clicking on the project in the solution explorer and go to "Properties-> Debug and change the target connection chain! If you are stuck on an older SSDT, then the instructions below will still work.

For SSDT 12.0.3 - I also suffered from this problem! My solution is lower, but it has some pros and cons ...

Decision

  • I assume that you are using a SQL Server project in VS (I am using VS2013 and SQL Server 2012).
  • Right-click on the .sql file in Solution Explorer and view the properties.
  • Change the action of the assembly to No.
  • If the file is open for editing, close it.
  • Re-run the file and the T-SQL editor should appear at the top.
  • Click "Connect" and you will connect to your (localdb).
  • Click "Disable."
  • Click Connect Again and the Connect to SQL Server dialog box appears.
  • Switch the connection string '(localdb) \ Whatever' to '.' (for some reason, using "(localhost)" did not work for me).

Voila, now you can query your SQL Server databases! Rinse and repeat for each file you need using ...: /

PROFI

  • You can finally run queries directly on your SQL Server database
  • Your code can be organized in a beautiful VS-solution (SSMS does not allow folders!: /)
  • You can (after switching the "Action" function). Create a project

Cons

  • I do not see autocomplete / intellisense for the remote db, although if you import your db then you can get intellisense from this
  • Each file is required to switch Build Action to None
+4
source

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

What I'm doing is Tools-> SQL Server-> New Query. Enter the database credentials (and make sure the database above is correct - I have hundreds of sp in my master db on local :))

Copy the source code from the editor, paste it into a new query window. Then execute (CRTL-Shift-E). You can leave this scratch window open and docked for easy access for later versions.

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

-1
source

More articles:


All Articles