Query Designer not displaying in Visual Studio 2013

Question

I don't know if I'm completely missing something....

I've got a SQL2008 database with 2008R2 Express loaded.

In VS2012 if I right click a table and select New Query, I get the Visual Designer.

In VS2013, same database, I get a blank page

I've tried a fresh install of 2012 and 2013 onto a fresh Win8.1, same thing. SQL2012 Express, same. If I use an LocalDB option, again no tools. I've tried Ultimate to see if it's something not in Pro.

Also the "code only" query doesn't connect me to the file properly, so even if I accept doing the query by hand, I have to paste a connection string every time.

I'm expecting it to do as per VS2012, and the VS2013 pages in http://msdn.microsoft.com/en-us/library/vstudio/ms172013.aspx .

My database is an attached file from an ASP.NET project App_Data folder.

It's driving me mad, as my only current solution is to return to VS2012.

Answer 1

Hi Peter, in Visual Studio 2013 the query designer and database diagram features are no longer available from the Server Explorer. If you wish to keep using the query designer, the only other workaround for now is to access it from SSMS. 

The documentation is definitely incorrect and should be updated - I have opened a documentation bug 827041 so that the incorrect documentation is at least resolved. If you create a connect bug for this general issue or want to follow up on the status of this, the bug number may be useful.

Thanks again for raising this,

Kevin

Answer 2

Does the "workaround for now" mean that it will come eventually in VS2013?

I'm using a App_Data database file, which allows for quick copy/deployment from development machine to IIS server test bed. If I use SSMS I guess I'd need to keep attaching / detaching it to copy it etc. or accessing it from VS?

I'm using VS2012 again, as it's extremely useful in the development environment to make a quick update to data. ie. Code dropped a record in, error in the data, quick delete of the record, fix bug, re-run.

Answer 3

I'm not aware of any plans right now to have this implemented in VS2013, so unfortunately I should have just said "workaround".

I'm not sure what the requirements are for attaching/detaching your DB in terms of how you develop your application, so it's a little hard to give advice on your follow up question. If your DB has been attached to a server (such as LocalDB, SQL Express or a full SQL Server instance), you'll see it in the list of databases when you connect to the server in SSMS. Therefore you might be able to continue your regular dev process in VS2013 but switch to SSMS when running the query designer, but I couldn't guarantee that would work without more info.

Answer 4

Thanks Kevin.

I'll have a play around and see whether its as easy to work with VS2013+SSMS as what I'm doing with VS2012, and then post how I got on.

Answer 5

Ok, been messing about with some alternatives.

On my Dev Machine I've got a straight forward ASP.NET project with a SQL2008 Express database (file) in an App_Data folder.

On my test bed I've got IIS + SQL2008 Express.

Currently - VS2012 and SQL2008 User Instance:

• Everything within VS
• Quick visual DB editing ie. a bug stores a field wrong, jump in and correct - using visual query and jumping into data, change, and save.
• One-click deployment using Publish straight onto my test server, with or without current data set. I might do this 10 times in an hour during an analysis / review session
• Switching data sets is easy with a straight file copy.
• Easy to copy full project to laptop for offsite development.

I've tried the following alternatives, but each has it's downfalls.

VS2013 and LocalDB + SSMS:

• Database using LocalDB.
• Can design tables from VS2013.
• Can do a code based query, but no option for manually adjusting data afterwards
• Firing up SSMS I can connect to same file database
• Can do Visual Query, but have to do New, then right-click, then select design.
• Once I click ok, dropped back into code.
• Have to reselect code, right click etc. to visually edit.
• Each time I deploy, SSMS loses the connection - have to refresh, but have to refreshing in VS first.

VS2013 and SQL2008 (User Instance) + SSMS

• Database using SQL Express User Instance with attached database file
  
• Can visually design tables from VS2013.
  
• Can do code query, but no option for interacting with the resulting data afterwards
  
• In SSMS, can't connect to that instance

VS2013 and attached SQL2008 database + SSMS

• Database fully attached to SQL Express.
  
• As above, design tables, but not queries in VS2013
  
• In SSMS can connect and visual query / edit etc.
  
• Publish in VS2013 no longer works due to file in use (have to stop/start service etc).

If someone has an alternative solution, I'd really welcome giving it a go, and this is really useful functionality for me that's been removed.

Answer 6

Have found a solution!!!

Two connections from Visual Studio 2013.

1. Microsoft SQL Server Database File (SqlClient) - connect to the file

• Software also using this for the connection string - works locally and publishes to IIS Server fine.
• Can work with tables visually.

2. Microsoft SQL Server + .NET Framework Data Provider for OLE DB

• Can work with visual queries.
• Can't save queries for repeating, but can cut/paste between query on option 1 and save there.

Conclusion

• All the visual tools
• Quick edits to data
• One click publish
• No closing connections
• All without leaving VS2013

Closing ponder - if all that's available, why am I having to split it in two????

Answer 7

Well. Quite unexpected that the visual query builder tool had to leave the Studio.

As I have to maintain quite complex (but older) software all day that has a lot of fairly complex queries, that I formerly could do with ONE tool (VS) and a few clicks.

Reason for me NOT to use VS2013 anymore. (Back to VS2012!)

Answer 8

Thanks Peter for your post in the first place!

I miss the visual query designer as well!

Answer 9

It is available in VS2013.  You have to use Server Explorer instead of SQL Server Object Explorer, and your Data Connections in Server Explorer have to use the .NET Framework Data Provider for OLE DB instead of .NET Framework Data Provider for SQL Server.  From there, the query designer works as before.

Answer 10

Well, i have 2 machines with Visual Studio Ultimate Update 4 installed and I have noticed differences.

Both are running Windows 7.

In one PC, I can see the query designer. In the Server Explorer, if i select a data connection and right-click a table, i see the usual options that I already had in VS 2012 : "Add new table", "Add new trigger", "New query", "Open Table Definition", etc.

In the other PC, there is none of that. Just properties ...

Answer 11

This one helped, thank you.

Answer 12

What if your database is running in Azure?  The authentication options for OLEDB are limited to SQL Server logins and Windows Authentication.  I need Active Directory Password Authentication.