question

Leo avatar image
Leo asked

'Open table' option in SSMS 2008

Hi Guys,

I have found the very strange features in SQL SSMS 2005 and 2008. Let say I choose one of the table in SSMS 2008 and right click on the table I want to open. I ONLY have the option for 1. Select Top 1000 Rows 2.Edit Top 200 Rows

NOT OPEN option.

I saw some server has installed same version of SQL 2005 and 2008, they have just OPEN option and there is no SELECT TOP 1000 / EDIT TOP 200. (that is great and what I wanted)

Anybody know why? I would like to have OPEN option only on my Development Machine because I can view and also edit the data on the same time.

Anybody know how to change the option to OPEN ONLY? I am using Developer Edition of SQL 2008.

Thanks.

sql-server-2008ssms
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

4 Answers

·
Rob Farley avatar image
Rob Farley answered

SSMS 2005 has Open. SSMS 2008 has Edit. But the two are the same. Open always meant Edit, but too many people would use it instead of SELECT. Changing it was one of the best things that Microsoft did with SQL 2008.

If you want the behaviour of Open, set the numbers of rows option in SSMS 2008 to a very large number, and use Edit. I would recommend learning T-SQL queries instead though.

Using SSMS 2005 on SQL 2008 is not recommended, as many new features won't work.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ ·
That's data admin, you ought to pass the details along to the system users and get them to use the application to update the record. They may need to do other stuff to manage the account properly. DBAs shouldnt edit data 'in passing'.
1 Like 1 ·
sp_lock avatar image
sp_lock answered

Not sure why it is different? But you can change the TOP within...

Tools | Options | SQL Server Object Explorer | Commands ....

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo ·
Hi Jonlee...Thanks. That is how I can limit the rows. The different is I dont need to open the table twice if I want to change the value in the table. For example..I am looking at the data in Table A by selecting SELECT TOP 1000 Rows, then if I want to change the Value for Column A then I need to close the table and Open again with Edit Option.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

OPEN was available in SQL 2000 Management Studio - I have never seen this in any version of SSMS past 2005.

Are you sure you saw it on a 2005 or 2008 SSMS?

5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock ·
Kev, I have seen it in 2005.. I think it is to do with a RTM of SQL Express edition.. No sure though...
0 Likes 0 ·
Leo avatar image Leo ·
Hi Yes Kev, that's why I do want that option.
0 Likes 0 ·
Leo avatar image Leo ·
By the way, The version I saw which is SSMS 2005 Developer Edition but I installed on another machine with same CD but I dont get that option.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ ·
Leo: was this an RTM version or CTP?
0 Likes 0 ·
Leo avatar image Leo ·
RTM version Kev
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

Is it just me that worries about data changes via the data grid? That never seems the right way to make alterations...

I have only seen the Open option in SSMS that came with SQL 2000

4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder ·
If you, and others, are poking around in edit mode be mindful of the locking going on. You may be affecting other queries against the system. I've seen a group of developers have several of these windows open and, at the same time, wonder why their queries are blocking
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ ·
+1 I would definitely recommend avoiding this way of working!
0 Likes 0 ·
David Wimbush avatar image David Wimbush ·
+1 Absolutely. I was a developer (still am a bit) and data bound automagically to a grid was never a good thing. Too many strange side effects in specific circumstances. Also, I believe you still have to move out of the cell before the update registers. It's the only way the grid can tell you've finished changing that value.
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman ·
I frequently use the data grid in test scenarios to quickly tweak small data tables the way I need them for a specific test run. I would never use it for data that was in production, and even in test environments it can get slow and awkward if the tables are large.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.