February 24, 2011 at 9:29 am
I'm on a new gig, and lately I'm having an issue in SSMS where, after having changed the database dropdown to the database I'm currently working in... if I choose Select Top 1000 Rows on another table, the context changes back to master.
It's a PITA if I start writing T-SQL and don't want to use the fully-qualified object name.
Any thoughts on why this would happen?
February 24, 2011 at 12:06 pm
SSMS sets the context to the currently selected (highlighted) db. It sounds like you are leaving the word 'databases' selected or some other node, rather than the db of choice. If you expand out the desired db and right click a table name, this does not change the selection, therefore doesn't change the context.
So before working on a lot of queries with the hope of opening a ton of windows, just click the database name first. Then expand the necessary trees and work as normal.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
February 24, 2011 at 12:32 pm
Jim,
Thanks for you suggestion, but it's not working for me.
If I click on the database Test, the dropdown up top left (available databases) becomes grayed out. But if I right click on the database and select "new query", it changes to "Test" and I'm in there.
But as soon as I select a table and right click -> select top 1000 rows, the context changes to master.
I suspect it may be related to permission settings.. not sure what, though.
February 24, 2011 at 12:37 pm
I do have master set as my default database. I just don't remember having this issue before, but then I'm not used to jumping around in a lot of different databases. My last couple of projects involved primarily one database (data warehouse).
February 24, 2011 at 2:30 pm
I think that's the standard "Select Top 1000 Rows" Right-click functionality. It does fully qualify the table name so even though the db is set to master, the rows are returned without having to switch dbs.
For better right-click query short cuts, I highly recommend SSMS Tools (http://www.ssmstoolspack.com/). The right-click functions include SELECT, CREATE, INSERT, etc. You can sent the results scripts to a new window, which does set the correct db, clipboard or file. There are also lots of options to play around with and I love the local SQL query history. I can pull up some query that was research for a coworker that I didn't think was worth saving in a file when they come back days later to ask me to re-run it. It's a great add-on to SSMS.
Marcy
February 24, 2011 at 3:10 pm
That is the default functionality. I'm seeing it too.
Why not just type 'SELECT TOP (1000) * FROM MyTable' and then you can take direct control over everything that happens?
For a quick way to mitigate the problems caused here, CTL-U will set the focus to the database window. If you then select the db you're intersted in and hit enter it will set focus to that database and switch the cursor back over to the last query window. Not perfect, but it's something. If you're manipulating the TOP 1000 query, just put a 'USE MyDatabase' statement at the top.
Sorry, that's about all I can think of.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply