Monday, March 12, 2012

Open empty environment

What happened to the Query Designer? It's a modal window that doesn't allow you to drag tables from other databases into the query. SQL 2000's verion of Query Designer was so much better than 2005. What happened? Are the developers at Microsoft completely retarded?

Also, why do I have to log into my database servers everytime I open "Server Management Studio"? Who thought up that nice feature? Probably the same brillant folks that redesigned Query Designer. Why would you want to have a forced login procedure for every database everytime the program is opened. Once again Microsoft must only hire retarded developers. Give yourselves a pat on the back!

Seriously, I think the SQL 2005 team owns stock in a MySQL tools firm.

Please, someone needs to wake up in Redmond - resolve these serious concerns in a service pack.

Dear Angry SQL 2005 User,

First, you tone is complete unnecessary. It is possible to get your point across without insulting people. This is not a threat but a promise: if your tone continues to be abusive I will delete your postings and have your account shutdown. The forums are designed to provide a place for fruitful discussion between community members – it is not a place for abuse.

Second, I don’t understand your comments about the query window being modal. The query window in Management Studio (SSMS) is a document window which, by definition, is non-modal. Also, you can drag-and-drop tables and views from Object Explorer to the query window surface. Can you provide more details on why you think it’s modal and what specifically you’re looking for in drag-and-drop.

Third, you can suppress the login dialog at start-up by changing your user configuration. The default configuration is to “Open Object Explorer at startup”. Opening Object Explorer requires a connection to a server. If you want to just startup the environment without being prompted for a connection and then choose a server to connect to from Registered Servers do the following:

1) Launch SSMS

2) Go to Tools -> Options

3) Select “Environment” – it should be selected by default

4) Change the “At startup:” option to “Open empty environment”

This option will restore your window settings but will not pop the connection dialog. For example, I have a bunch of servers registered that show up in the Registered Servers tool window. When I launch SSMS with the “Open empty environment” option, SSMS loads without the connection dialog and the Registered Servers is populated. I can then select a server to connect to at which time I get the connection dialog.

I hope this information helps.




Thanks for the quick reply.

Your reply is probably referring to a different part of SSMS, I was referring to the GUI tool used for creating queries automatically without actually handcoding SQL code. In SQL 2000 the tool is very flexible and easy to use. To make sure we are talking about the same thing - In SQL 2000 just right click on a table and select 'open table' and then 'query'. This opens up the GUI query generator. In SQL 2005 to open the new GUI tool called Query Designer you select 'query' on the toolbar and then 'design query in editor'. This was the tool I was referring to, not the sql script window where you hand type code.

The Query Designer in SQL 2005 indeed locks the SQL 2005 screen (I'm not sure if it's called modal, however, in SQL 2000 the screen was not locked). Also, I'm using the production version of SQL 2005.

The queries we write span databases, therefore, the ability to drag a table from another database is crucial. Also, our queries are extremely complex and without the SQL 2000 technology we will have to write views to 'see' these other databases in the Query Designer. This is not a big deal, it's just that SQL 2005 is less flexible in many aspects. Since we are paying good money for the upgrade I think someone in Redmond should know that a few changes would make many folks happy.


Angry SQL 2005 User,

Okay we're on the same page now wrt the query designer. Let me do a little more research on the subject and post back.


p.s. thanks for toning it down!


Angry SQL 2005 User,

Here's what you need to do. In Management Studio select a table, right-click and select "Open Table". This will open up the table showing the contents of the table in a document window. Now, in the doc window right-click and choose the "Pane" option. Here you can turn on the Diagram, Criteria, and SQL panes. Now you can drag-and-drop tables and views from different DBs on to the surface. You can also turn on these panes from the "Query Designer" toolbar.

I believe this gets you what you want. If not let me know.




Thank you for the quick response.

Everyone at work is happy now!


Hey Dan,

Thanks for setting that guy straight.

My problem, I followed exactly what you outlined for the Query Designer, However, I cannot drag and drop table on the the Diagram Pane, I just received a "null" sign when dragging the table on the pane. Is there an option somewhere to allow drag and drop. I did this successfully with the previous version (2000).

Thanks in advance,



Hi Dan, good day.

I did what you say to stop prompting each time I open a saved query, but it does't work at all, SSMS keeps prompting to connect each time I open a query, this did't happen in the query analyser, why is this?

Thanks and regards, happy holydays


Alfonso, My instructions were to stop SSMS from prompting when it's launched. When you open a new query you must either already have a connection established or SSMS will prompt you for the connection information. There is no concept of a delayed connection when authoring queries.



Hi Jim, That's very odd. Let me ask a few folks around here what the problem could be. Your installation my be messed up. It may take me until next week to get an answer as lots of folks are off this week.



To clarify a bit on what Dan said there are a few different ways query windows can operate, we attempted to do the "right thing" but you can always undo what we did if it isn't what you wanted.

If you ask for a new query window via File->New->Database Query (or any other), or the toolbar button to the RIGHT of the "New Query" button, we ALWAYS prompt for a connection, you can press cancel and you will get a query window that is disconnected. We will not prompt for a connection until you attempt to execute that query or ask to connect explicitly via the Query->Connection->Connect option, or toolbar button under the New Query button.

If you ask for a new query window via File->New->Query With Current Connection, or the New Query button, or Ctrl+N, then we create a new query window with whatever current connection context you have. If the focus is in a Query Window we will create a new one with the same connection (this is the same behavior as QA). If you have focus in Object Explorer or Registered servers on a server, we will create a query window connected to that server.

Finally if you ask for a new query window with the current connection (as above) but there is NO current context: no query window, focus on a disconnected query window, focus on the server type node in the registered servers window then we will prompt you for a connect and in this case, if you press cancel, the new query window generation is stopped.

If we end up connecting you to a server you didn't want or not connecting you at all you can click on the disconnect toolbar or the re-connect toolbar (or their associated menu's under Query->Connections) and get the query window to the right server.

Finally there are some context menu's to be aware of. From a registered server you can right click and go into the "Connect" menu and select Query Window. This will open a query window connected to this server (regardless if it is connected in Object Explorer).

From a connected server in OE you can right click and use the New Query menu option to open a query connected to that server. If you select a database in the Object Explorer and do a New Query from there, then you will be connected to that server with the selected database as the current database.

These same concepts apply to an Analysis Server connection just as they do to a Relational Server connection.

Play around with it and hopefully it will allow you to achieve what you would like, when you would like it.


By the "null" sign I assume you mean the circle with the cross through it... yes?

If this is the case you can get this if you are trying to drag tables onto the diagram that are from a different database then where the diagram is stored. We don't have cross-database diagrams.

Does this help?



I have been trying to do "open table" ever since I installed sql server 2005 in Decemeber. However everytime I select a database, select a table and right click and select "open table" I get the error "Object reference not set to an instance of an object". Next I would like to be able to edit the data directly in the results pane once this works.

Reading in different forums I am not sure this is still an option.

Also I have loaded the Adventure Works database and tried to "Open Table" and I get the same error above.

I am frustrated because there are so many little things missing or not working in "sql server 2005" that I regret even installing it. In sql server 2000 I could select an access database and import it and the tables appeared. In sql server 2005 I get multiple errors. I finally resorted to writing my own code to copy the access database into sql server 2005.

Please let me know any help would be appreciated.


David, I'm sorry to hear you are having so many troubles. It definately sounds like something is definately wrong with your installation. The open table should just work and you can edit the results just like in Sql 2000. In fact this is exactly the same component that was used in Enterprise Manager and the open table feature there so the funcationality should be almost identical. I know there were some changes in this component but nothing major to my knoweldge (it isn't built by my team so I can't comment with absolute confidence here). I would highly recommend you contact Microsoft Support as it is unlikely something I could get figured out via e-mails.

I'd like to get more information as to what "little things are missing." As we continue to release new service packs for Sql we want to make sure and address as many of these types of things as we can.

One thing that we've also found is that because it is a bit different often the same functionality is available elsewhere in the tool and we just did a bad job of making it discoverable and once you know where it is, you'll find less things missing and they won't be hard to find... as you will have found them.

If you are willing to give me a list if you can make sure to articulate "how" you went about doing the things you miss. For example in your statement above you mention that you used to "import an access database" and then "the tables appeared." This is a little unclear. Do you mean you used the Import Data Wizard to import the tables and then those tables were in the Sql Server and you could work with them or are you talking about something else?

Enterprise Manager, Query Analyzer and SSMS are all very large tools and the "obvious" things we all do aren't necessarily obvious to somebody else because they do things slightly different. So the more information you can give us the better.

I would also recomend that you go to the feedback site and either vote on existing feedback entries or add new ones if you don't see what you find lacking.

"This posting is provided "AS IS" with no warranties, and confers no rights"



Thanks for the reply I appreciate it. The "open table" definitely does not work I now know you can edit data as in the old install and have seen someone else do it. when I right click and select new query and enter the query with a table name I get an error saying the object is not recognized. If I qualify the table name with the database name then it works. I tried to import an access data base and could not get it working. so I wrote the code and to do the import myself. When I delete tables tables or devices I have to hit the F5 key to refresh the view for the names to disappear. These are little things that worked in sql server 2000 that somehow don't in 2005.

I have backed up my databases and am going to unistall and reinstall and see if that gets rid of the open table issue.


No comments:

Post a Comment