SSMS Available Databases DropDown Acts weird if Connection Properties Are Changed

Posted on : 11-09-2012 | By : Devi Prasad | In : SQL Server Issues, SQLServerPedia Syndication

Share:

1


SQL Server Management Studio (SSMS) Has a available databases dropdown ( to select a database to execute the query.).

Available Databases Dropdown in SSMS

This dropdown behaves weird when you change the connection properties.

Observation 1:
Run the below query in the query window

SET PARSEONLY ON

Now when you try to expand the dropdown, the drop down does not expand.

Run the below query to turn off PARSEONLY

SET PARSEONLY OFF

Observation 2:

Run the below query in the query window

SET ROWCOUNT 2

Now if you try to click on the dropdown, you will find only 2 available databases.

Run the below query to reset ROWCOUNT to 0

SET ROWCOUNT 0

Why is the dropdown behaving this way?
SSMS is using the same connection used by the query to fetch the Available databases list.
SSMS runs the below command to fetch the results for the Availabale Databases DropDown, using the same connection used by the Query.

SELECT dtb.name AS [Name], dtb.state AS [State] FROM master.sys.databases dtb

Also, You would also get wrong results count into the @@ROWCOUNT, when you run the query and accidentally touch the availabale databases dropdown.

Observation 3:
Run the below query

SELECT * FROM SYS.OBJECTS
SELECT @@ROWCOUNT as ROWSCOUNT

Make a note of the ROWSCOUNT Result.

Now run the query

SELECT * FROM SYS.OBJECTS

Click on the available databases dropdown and run the below query.

SELECT @@ROWCOUNT as ROWSCOUNT

You can identify the difference in the values of @@ROWCOUNT.

The first result would be the number of objects in the database.
The second result would be the number of databases in the Server.

(Visited 86 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (1)

hi sir,
now i am studying sqlserver basics..Now i need explanation about joins types

Write a comment