This morning when I tried to connect to my local SSAS 2012 (RTM) Tabular instance in the Object Explorer in SQL Server Management Studio I got the following error message:

TITLE: Microsoft SQL Server Management Studio
——————————

Error connecting to ‘localhosttabular’.

——————————
ADDITIONAL INFORMATION:

Unable to establish server mode. (ObjectExplorer)

This was an error I’d seen before several times (as had Alberto – see here – and a few other people), and had previously associated it with memory issues and/or corrupted databases. However in this case I was confused because I was still able to query the SSAS Tabular instance in question from Excel and deploy to it from SQL Server Data Tools, so I was pretty sure there wasn’t anything wrong with the instance.

After a bit of looking around I found what the problem was. A few days ago I had been using SQL Server Management Studio to write queries against a workspace database and I had set the name of the database in the ‘Connect to database’ dropdown on the Connection Properties of the Connect to Server dialog, as shown below:

Now the SSDT project associated with this workspace database had its Workspace Retention property set to ‘Unload from Memory’, so when I closed the project the workspace database disappeared from the server. However in SQL Management Studio the Connection Properties dialog was still trying to connect to the database even though it was no longer there, and instead of giving a sensible error message it was giving me the “Unable to establish server mode” error. Fixing the problem was simple: I just went to the Connect to database dropdown and selected “default” instead of the name of the workspace database.

I would imagine that a lot of people will run into this issue, not only when they have been connecting to workspace databases but also if they connect to regular databases that are subsequently deleted. Hopefully this blog post will save them a lot of time spent wondering what’s gone wrong!

Article source: http://feedproxy.google.com/~r/wordpress/Cpjz/~3/BeNE5ZXQEOE/