{"id":258,"date":"2012-09-11T11:55:55","date_gmt":"2012-09-11T11:55:55","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/09\/11\/ssms-available-databases-dropdown-acts-weird-if-connection-properties-are-changed\/"},"modified":"2012-09-11T11:55:55","modified_gmt":"2012-09-11T11:55:55","slug":"ssms-available-databases-dropdown-acts-weird-if-connection-properties-are-changed","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/09\/11\/ssms-available-databases-dropdown-acts-weird-if-connection-properties-are-changed\/","title":{"rendered":"SSMS Available Databases DropDown Acts weird if Connection Properties Are Changed"},"content":{"rendered":"

SQL Server Management Studio (SSMS) Has a available databases dropdown ( to select a database to execute the query.).<\/p>\n

\"\"<\/a>
Available Databases Dropdown in SSMS<\/figcaption><\/figure>\n

This dropdown behaves weird when you change the connection properties.<\/p>\n

Observation 1:<\/strong>
\nRun the below query in the query window<\/em>
\n[sql]
\nSET PARSEONLY ON
\n[\/sql]<\/p>\n

Now when you try to expand the dropdown, the drop down does not expand.<\/strong><\/p>\n

Run the below query to turn off PARSEONLY <\/em>
\n[sql]
\nSET PARSEONLY OFF
\n[\/sql]<\/p>\n

Observation 2:<\/strong><\/p>\n

Run the below query in the query window<\/em>
\n[sql]
\nSET ROWCOUNT 2
\n[\/sql]<\/p>\n

Now if you try to click on the dropdown, you will find only 2 available databases.<\/strong><\/p>\n

Run the below query to reset ROWCOUNT to 0<\/em>
\n[sql]
\nSET ROWCOUNT 0
\n[\/sql]<\/p>\n

Why is the dropdown behaving this way?<\/strong>
\nSSMS is using the same connection used by the query to fetch the Available databases list.<\/em>
\nSSMS runs the below command to fetch the results for the Availabale Databases DropDown, using the same connection used by the Query.
\n[sql]
\nSELECT dtb.name AS [Name], dtb.state AS [State] FROM master.sys.databases dtb
\n[\/sql]<\/p>\n

Also, You would also get wrong results count into the @@ROWCOUNT, when you run the query and accidentally touch the availabale databases dropdown.<\/em><\/p>\n

Observation 3:<\/strong>
\nRun the below query
\n[sql]
\nSELECT * FROM SYS.OBJECTS
\nSELECT @@ROWCOUNT as ROWSCOUNT
\n[\/sql]
\nMake a note of the ROWSCOUNT Result.<\/p>\n

Now run the query
\n[sql]
\nSELECT * FROM SYS.OBJECTS
\n[\/sql]<\/p>\n

Click on the available databases dropdown and run the below query.<\/p>\n

[sql]
\nSELECT @@ROWCOUNT as ROWSCOUNT
\n[\/sql]<\/p>\n

You can identify the difference in the values of @@ROWCOUNT.<\/p>\n

The first result would be the number of objects in the database.
\nThe second result would be the number of databases in the Server.<\/p>\n","protected":false},"excerpt":{"rendered":"

SQL Server Management Studio (SSMS) Has a available databases dropdown ( to select a database to execute the query.). This dropdown behaves weird when you change the connection properties. Observation 1: Run the below query in the query window [sql] SET PARSEONLY ON [\/sql] Now when you try to expand the dropdown, the drop down…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[186,3],"tags":[],"class_list":["post-258","post","type-post","status-publish","format-standard","hentry","category-sql-server-issues","category-sqlserverpedia-syndication"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/258","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=258"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/258\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=258"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=258"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=258"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}