Knowledgebase
Error 916 When Connecting SQL Server 2008 Management Studio To SQL Server
Posted by Sean Syed on 21 February 2013 07:09 PM

SYMPTOMS:

When you trry to connect to database servers where you are not in full control over all databases, or where some of your databases are ever offline (or auto-closed), the new version of Management Studio that is shipping with SQL Server 2008 is going to bring you some surprises, when you try to present a list of databases in Object Explorer.

The problem is that for a database that is offline or has been auto-closed, collation comes back as NULL.  Well, that's not really the problem... the real problem is that SSMS throws its hands in the air when it comes across NULL for these values, and assumes this is NULL because you don't have permission.  So it throws up this error:

SQL Error 916

And then refuses to show the data for ANY database, instead of just hiding the one(s) that caused the error.

WORKAROUND:

Databases that are hosted by 3rd party providers, some of which are accustomed to leaving as many databases in auto-close mode as possible,

  1. Highlight the databases node in Object Explorer
  2. Switch to Object Explorer Details
  3. Right-click the column header list on the right (where it says Name, Policy Health State, etc.)
  4. Un-check the Collation option. (For those unaware of Object Explorer Details, go to View > Object Explorer Details, or hit F7.)

At this point, if you refresh the Databases node in Object Explorer, you should again see all of the databases on the server, even those where you don't have access.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).