gotdough
asked on
Directly editing data in results pane with SQL 2005 Management Studio
In SQL 2000 Enterprise Manager, one was able to edit and commit data on-the-fly directly from the results pane. Action->Open Table->Query with the SQL Pane shown gives you an interface similar to Query Analyzer. One could write a complex select statement with where clauses and joins, and the results pane would show the resulting data. The data is editable and instantly updated. We are now planning to migrate to SQL 2005, and so will need the same capabilities that were availiable from its predecessor. I believe there to be an option/configuration setting or panel in Management Studio that would expose this functionality. I have SQL 2005 Standard Client Tools installed with the SQL 2005 Express Engine, as well as SQL 2000 Client Tools.
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature?
The question is this: How do you edit data in the results grid in SQL 2005 Management Studio as you would in SQL 2000 Enterprise Manager when you Open Table via Query (Action->Open Table->Query), and what option/configuration setting or panel would you need to enable/show to make this a permanent feature?
ASKER
Those links explain in great detail what is updateable, but what do you need to do to create a new query that returns an updateable results pane?
So I open SQL 2005 Management Studio, login to the server, click the "New Query" button, type in a basic select query such as "SELECT * FROM taFooBar", and hit the "!Execute" button. The results pane is not updateable. This behavior is akin to Query Analyzer. In the old Enterprise Manager, I would do Action->Open Table->Query, type in the same query, and the results return as updateable.
Basically, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make the default setting?
So I open SQL 2005 Management Studio, login to the server, click the "New Query" button, type in a basic select query such as "SELECT * FROM taFooBar", and hit the "!Execute" button. The results pane is not updateable. This behavior is akin to Query Analyzer. In the old Enterprise Manager, I would do Action->Open Table->Query, type in the same query, and the results return as updateable.
Basically, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make the default setting?
ASKER
Does anyone know how to do this?
ASKER
It seems that Management Studio is missing this feature. Can anyone confirm?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Very cool.. thanks for this.. but another question.. when you do "open table" in management studio, it instantly does a select * which is quite nasty on a large table.. and I'm forced to click stop before I can continue forth, edit the sql, and rerun to do my results edit... is there a way to prevent management studio from instantly doing a select * on the open table function?
Thx,
-Steve
Thx,
-Steve
Found answer I needed from msdn forum:
--------------------------
I found a solution!
MS removed the "Open Table -> Query" from the menu, but apparently the functionaliry is still there.
I found it best to first create an EmptyTable in the database, named so that it's near the top of the list in Object Explorer.
Right-click the EmptyTable and do an Open Table. Since it's empty, it should take any time. Now show the "Query Designer" toolbar. Then click the square "SQL" button, with tooltip that reads "Show SQL Pane". Ta-da.
If anyone finds a quicker way to get this layout, let us all know.
--------------------------
I found a solution!
MS removed the "Open Table -> Query" from the menu, but apparently the functionaliry is still there.
I found it best to first create an EmptyTable in the database, named so that it's near the top of the list in Object Explorer.
Right-click the EmptyTable and do an Open Table. Since it's empty, it should take any time. Now show the "Query Designer" toolbar. Then click the square "SQL" button, with tooltip that reads "Show SQL Pane". Ta-da.
If anyone finds a quicker way to get this layout, let us all know.
ok, I ran a test, I updated those other fileds to not have nulls. Then I re-ran my script and it works great! Except now it errors on another one that has null values on those other columns.
In fact, if I just update the one F4 Column, it does not error. Maybe I should jsut manually fix those fields...
In fact, if I just update the one F4 Column, it does not error. Maybe I should jsut manually fix those fields...
Not sure if I can tell you what you wanted to hear but if you look in you SQL Server 2005 Books Online, do a search for "Rules for Updating Results" - you wll find a table detailing when you can and can't edit/insert in the data grid.
fyi: On my pc, the link to this page is:
ms-help://MS.SQLCC.v9/MS.S
Also, search this page:"Working with Data in the Results Pane"
ms-help://MS.SQLCC.v9/MS.S
Basically, you can construct you query to span multiple tables, but making changes to the underlying data should be kept to a dedicated results window containing a selection from just ONE table. This ensures you can edit the data.
Lee