Open Table not editable, anyone know of a workaround? 2005 only.

[kxramse]

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. With a single table it works fine, but add multiple tables to the mix and it stops working. Add some joins, and forget about it. Do the same thing in 2000 and you're able to perform the task.

Lessons learned from previous answers to this question throughout the internet: (by me and others)
1. It doesn't seem to be a service pack issue. I have the latest on the client and the server.
2. We don't want to create a view, then change everything to an update statement, comitt it, and then delete the view again, or accumulate a ton of views.
3. Here is one example of the sad history of trying to get this issue resolved...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578917&SiteID=1

4. This issue has been asked in many forums with no answer that solves the crux of the problem. My understanding is that its the first time on tek-tips.

Any ideas? Permission issues that you've tested to confirm it works if you change some settings? (Please confirm.) FREE software tools that will perform this task?

 

[mrdenny]

In the SQL 2005 GUI you can edit the table directly in a grid format. Right click on the table and select open. If you want to filter the list, there is a "SQL" button at the top so that you can add a where clause and filter down the table like you could in the SQL 2000 GUI.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kxramse]

Like I said... With a single table it works fine, but add multiple tables to the mix and it stops working. Add some joins, and forget about it. Do the same thing in 2000 and you're able to perform the task.

 

[manmaria]

But if you step back and think about the edit option in joined tables, what is the use? Data is part of the individual table not with the material view.
Just my thought.

 

[kxramse]

It has been a long standing practice, and, unless we find there is a permissions issue, the feature was removed and people became reliant upon it. We need it back, or we need a free utility.

No one ever said it was the best idea.... Just a needed feature once it was supported by precedent from Microsoft.

 

[mrdenny]

The problem is that the SQL 2000 and SQL 2005 GUI were never designed as data entry tools. They are managment tools that happen to be usable for data entry.

You can submit the request to Microsoft via the Connect web page at

http://connect.microsoft.com/

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kxramse]

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=148844

Ughh. They confirmed they removed the feature. How unfortunate since they established the precedent.

Any chance you know of free third party tools that can make this work?

 

[mrdenny]

Nope.

That bug report is still open. The more people that vote on the problems the greater chance that they are fixed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kxramse]

I know its not free, but would TOAD work?

Maybe we will settle for the least expensive alternative???

 

[mrdenny]

It might. I don't use TOAD so I'm not sure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com