Wednesday, March 21, 2012

Open Table > can't add data into the new row of the grid

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? In other words, what is the equivalent of Action->Open Table->Query in Management Studio? And how do you make it the default setting?

Can this be done in SQL 2005? Or only in SQL 2000?|||You can use the Open Table functionality to modify the data in a table or schema-bound view. To do this, right click on a table (or view) and select Open Table (or Open View).|||I'm looking for Open Table via Query, not just All Rows.|||bump|||

Open a SQL Editor window, type some text, highlight the text, and select Design Query in Editor. This will bring up a modal dialog containing a Query Designer with your SQL.

Is this what you were looking for?


I too have spent hours trying to figure out how to do tasks that were easy in SQL 2000 EM. If I open a table, it retrieves all rows, not something I want to do with a remote server.

I'd like to query a table, and be able to add or delete records, as well as edit data. SQL Management Studio seems to be lacking this feature of Enterprise Manager and Query Analyzer.

If I save a query to an .sql file, I can't add new records. There's got to be a better way.


Can you safely use SQL 2000 client tools (Enterprise Manager/Query Analyzer) with SQL Server 2005? Is that a viable alternative to Management Studio?


I searched for a possible solution and the only one is using SQL 2000 Enterprise Manager. If you want to use Management Studio this is bad, because we were using this feature for querying one or two records and changing some simple data. Now we have to write update queries, be aware of rowcount, take care of transactions, etc. Maybe this is the way MS wants people to do.

Also our DB admins will not be happy when developers use "Open Table" in Management Studio and query all data in tables which has millions of rows.


I created a small table with just a few records. I open the table, and then paste my SQL query into the SQL pane, so I can query any table I want. I'll probably create a few more small tables so that I can use them for opening multiple queries at the same time.

This gets me by for now. I suspect Microsoft wants me to develope my own application or web pages to manage data, or use MS Access. It just seems that Management Studio should do the basics, and do them well. Wordwrap and pressing the INS key to toggle insert mode in the results pane don't work either, by design i've been told, which is unbelievable. I guess as a server product it's not designed to be as user friendly as I expect.


I can't get the "open table" command to work. Was I suppose to set this function up during install or is it something within SSMS?

|||In SQL Management Studio, just right click a table.|||


When I tried doing this I got this error,

"Object reference not set to an instance of an object. (SQLEditors)"

This doesn't tell me anything.

|||I was running SSMS remotely, apparently it doesn't like this. When I remote to the server then run SSMS it works fine.|||

The 'Open Table' command opens a grid with a * 'new row' at the end.
When I enter some data there it says always:

No row was updated.
The data in row 1 was not commited.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The update row has changed or been deleted since data was last retrieved.
Correct the errors and retry or press ESC to cancel the change(s).

If I link that table from an Access database I can enter new data as it should be.

What is wrong here?

No comments:

Post a Comment