Greetings, all. Thanks for all of the great postings in this community. However, one question I have doesn't seem to have been addressed. In SQL 2000 Enterprise Manager, a user could right-click on a table, and choose Open Table>>Query. This option brings the user directly to a query designer very similar to the query designer available in SQL 2005 Management Studio. However, I have been frustrated by the following:
- The Open Table option in SQL 2005 returns data immediately where the SQL 2000 Query option did not, meaning it opened faster and did not risk locking any records in the table
- In SQL 2005 the user needs to click three buttons to open all of the Panes which the "Open Table>>Query" option in SQL2000 had done automatically
This may seem like small stuff, but when a developer like me spends most of his time building queries which link several tables, this builder is priceless and time is precious.
Is there some trick in SQL 2005 go to the Query Designer with all panes open and no data returned automatically? If not, can someone point me to where I can make this request for a future service pack for SQL 2005?
Thanks,
Ron D.
St Paul, MN
Hello Ron,
Thanks for your feedback. I am a Program Manager in Visual Studio Data team and I am the one to talk to about Query Designer (both in SSMS and VS).
Within SSMS, Query Designer is activated via Open Table command. Most people use this command to browse through data within the selected table. So that’s why the default view is the results pane.
If you are within Visual Studio 2005, Query Designer is activated via New Query or Show Table Data from Server Explorer. You can also bring up Query Designer from Solution Explorer if you are within a Database Project. Here, New Query option defaults to all panes activated. And Show Table Data defaults to only results grid.
We were trying to provide most relevant default behavior depending on where you activate Query Designer.
Within Visual Studio, you can actually change the default layout by going to Tools -> Options -> Database Tools -> Query and View Designers. This option is missing from SSMS and we have it as potential future enhancement.
Lastly, you can activate/deactivate each pane very quickly by hitting CTRL+1 (Diagram Pane), CTRL+2 (Criteria Pane), CTRL+3 (SQL Pane) and CTRL+4 (Results Pane).
Hope this helps.
|||Thanks for the response, Mr. Joo. I appreciated the shortcut keys.
I wasn't very specific, but I do hope that the Management Studio interface can be improved related to Query Designer in a future build. In development, I will often write code to change data in a table, but during testing I will have to make many manual changes to data in tables so I can test and retest. The results grid in Mgmt Studio using "Open Table" seems to be the only place to do this without writing an update statement. Again, it would be nice if the option to design a query were available without returning data initially and while retaining the ability to edit data in the results grid.
I also noticed that I can't change the query type from the "Open Table" feature in the Management Studio object browser. I hope this will be enabled in the future, to allow my to draft quick delete and update statements from data I already have filtered in an "Open Table" query.
I develop with a proprietery development language (Great Plains Dexterity), so I don't have access to the options in Visual Studio.
Thanks again,
Ron
|||Ron,
Thanks for your feedback. It is our goal to provide consistent experience to both developers and database administrators regardless of which IDE they choose to use. You will see some of that in our future versions of Visual Studio and SQL Server Management Studio.
Regarding disabled 'Change Type' feature, we intended Open Table to be used as a data browsing tool instead of data management tool. If you bring up a Query Builder from SQL Editor window by choosing Design Query in Editor from right-click menu, you will be able to morph queries.
Thanks!
|||
Young,
I have been trying on and off to get right click and get "Open Table" to work. I always get the error message "Object reference not set to...". What causes this. Also quite a few times when I right click and select new query then type in sql with tables names I get an error, then if I qualify the name of the table with the db name like so fooDb.foo the query will run. Also in sql server 2000 when I imported an access db it worked perfectly and I could view the tables. In management studio when I do this I get nothing but errors. I finally wrote my own code to copy the access db in.
Sorry but at this point I am a frustrated sql server 2005 user who wants to uninstall.
|||Hello David,
Sorry that I missed your question. Hope you have not uninstalled SQL Server 2005 during past month. :)
Three issues you described.
1. "Object reference not set to..." error during Open Table
2. Error message in new query without fully qualified table name
3. Opening Access tables
I will have to do some research on #1 and #3.
For #2, I am pretty sure that you are connected to master database when you open up a SQL Editor for new query. That's why it works only if you qualify your table name with database name. You can either type 'use <dbname>' and hit CTRL+E to change the database or use the drop-down in the toolbar area.
I will get back to you with more information on #1 and #3.
Thanks!
|||Open Table brings up a restricted functionality Query Designer that is intended just to show the data in a table. The easiest way to get to the full-functionality Query Designer in Management Studio is through a T-SQL editor instance.
You can open a new query editor in Management Studio by clicking the New Query button, by clicking the File > New > Query with Current Connection menu, by clicking the File > New > Database Engine Query menu item, or by right clicking on a database in Object Explorer and selecting New Query in the context menu.
If you use the context menu item on a database, the query editor is initially connected to that database. Once you have a query editory, you can right click in the editor and select Design Query in Editor... to start the Query Designer for the selected T-SQL. (If nothing is selected, the Query Designer starts in an empty state.)
Thanks,
Steve
|||The only way I got open table to work was reinstalling everything. The first time I installed I did get an error while install was "Setting file security" I canceled the install and tried again. In that install I had to unplug my network cable to keep "Setting file security" from hanging (found using internet search).
After the uninstall I am able to open the table and edit values. Howerver, I would like to be able to edit values not just using "open table" but within a query. This is how things functioned in sql server 2000 and it is something very useful.
And to stray for a moment in the sql server 2005 I cannot just import an access db like I could in sql server 2000. After all of the import steps the import fails. For this to work I wound writing my own code to do the import.
I would also request if anyone from MS reads this that the uninstall of sql server 2005 be done in one step. When I unistalled my corrupted version I had to unistall each component separately.
I have also had an install where management studio was not installed after selecting all of the components for install. On this instance iis was not present on the system. I have not researched why management studio was not installed but I will shortly.
Some of the features that I use on a day to day basis don't function reliably or not at all. I like the work that has been done in the product but would like edit results and also would like import of an access to work like it did in sql server 2000.
Thanks for the help.
|||
Mr. Joo
Please add my voice to Ron's. Query Designer is an incredibly efficient tool for quickly retrieving, viewing and editing data in large tables. It is also a great tool for quickly creating SQL to use in sprocs and other programs.
The ability to access the the Query Designer with the Diagram, Criteria, SQL, and Results pane fully intact and already showing the table(s) to query is an incredible time saver and should be made even easier to access than it was in SQL Server 2000.
I would love to even have the ability to highlight multiple tables and push a button to open the Query Designer with these tables preloaded in the Diagram pane.
Please consider this for the next Service Pack upgrade to SQL 2005. Its that important.
Sean
|||Steve,
This method is, as you say, the quickest way to access the Query Designer, but the results pane is absent and from what I've been able to find so far, the data returned after running the query in the T-SQL editor cannot be edited in the datagrid.
Sean
|||Hi
1. Right click on table and open, stop query and then switch on panes: diagram, criteria, sql - too many clicks.
2. Design Query in editor form SQL Editor - that is a blocking window (modal I think) and does not have results pane.
I belive in next release I could fast open query designer (not modal) in one click with visible diagram, criteria, sql and empty results pane.
Kamil
|||Thanks all for the tips on getting the Panes open off of the table browser object. I never would have found that.
I immediately fell prey to the Editor popup (with the Results Pane option disabled), first by right-clicking the table in the browser and choosing the "Script Table as ... SELECT * To ..." option and then "Design Query in Editor...".
This causes several problems. Since there is already SQL code generated by the "...SELECT * To..." option, you would think that it would use this code as the starting point in the Editor...it does not.
Secondly, you would think clicking "OK" on the editor would use this SQL code in the query window that it returns to...it does, but as an insert wherever you happened to have left the cursor before you selected to use the Editor, rather than as a replacement over whatever was there initially. Now that I know this is here, I can see where this might be handy for building nested queries, etc., but it is definitely not intuitive... and not available from the Open Table windows? What if that is where I'm wanting to include a nested query?
I like the addition of the Table Open option (like the Data tab in TOAD) rather than the SQL 2000 method of choosing a Select * or Query option, but why get rid of the Query option - other than as menuing through to add each Pane (or multiple upper-left Pane buttons).
The Query Designer without a Results/Parse option makes no sense at all. You can't tweak the results from this dialog to make sure you are getting what you want out of the code.
Growing pains...panes..I guess. Hope some of these can be addressed in SP2...?
No comments:
Post a Comment