A Fine Slice Of SQL Server
Chris Webb explains how to show hidden Analysis Services cubes in Management Studio:
Unfortunately this doesn’t make any objects in the cube that are not visible, like measures or dimensions, visible again – it just makes the cube itself visible. However, if you’re working on the Calculations tab of the Cube Editor in SSDT it is possible to make all hidden objects visible as I show here.
Read on for the command and watch out for that caveat.
Comments closedKendra Little shows off a really cool feature in SQL Server 2016 & 2014 SP2:
SQL Server now shows Actual Elapsed CPU Time and Actual Elapsed Time (duration) for each operator in an Actual Execution Plan
For SQL Server 2016 and 2014 SP2 and higher, actual execution plans contain a bunch of new information on each operator, including how much CPU they burn, how long it takes, and how much IO is done by that operator. This was a little hard to use for a while because the information was only visible in the XML of the execution plan.
Check out Kendra’s post for more details, including a couple caveats.
Comments closedJakub Szymaszek shows off Parameterizing for Always Encrypted in SSMS 17.0:
First thing to note is that SSMS has rewritten the query as a parameterized statement. The literal, used to initialize the
@SSNvariable in the original query, is being passed inside a parameter, with an auto-generated name (@pdf9f37d6e63c46879555e4ba44741aa6). This allows the .NET Framework Data Provider for SQL Server to automatically detect that the parameter needs to be encrypted. The driver achieves that by calling sp_describe_parameter_encryption that prompts SQL Server to analyze the query statement and determine which parameters should be encrypted and how. Then, the driver, transparently encrypts the parameter value, before submitting the query to SQL Server for execution via sp_executesql. SQL Server can now successfully execute the query.
Read the whole thing. Setting this up does obviate part of a benefit to using Always Encrypted: the ability completely to lock out a database administrator from certain pieces of data.
Comments closedAndrew Peterson troubleshoots an error after installing SSMS vNext:
After installing SQL Server Management Studio for vNext, the Configuration Manager no longer opens, with a message similar to the following:
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]
Read on for the solution.
Comments closedDenis Gobo shows how to specify a database when connecting to an instance using Management Studio:
One of our database on the development went in suspect mode today. This database was the default for a bunch of logins. These people could not login now. Someone needed to use a different database but he couldn’t login because the database that was in suspect mode was the default database for the login he was using.
I told this person to click on the Options button in the connection dialog and specify another database. I guess there was an misunderstanding because this person couldn’t get it to work. This means it is time for a blog post.
Connecting to the default database is usually fine, but sometimes you need to specify one. Fortunately, Management Studio makes it pretty easy.
Comments closedBut my Backup file is still not visible in the wizard!
Permissions. If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql. Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file.
The whole process is pretty straightforward.
Comments closedJay Robinson has a couple Visual Studio Settings files to change Management Studio font sizes:
I present technical sessions now and then – my local PASS group, SQL Saturdays, internal groups at my workplace, etc. I frequently find myself adjusting the fonts inside SQL Server Management Studio to make sure my material is readable on the big screen. I’ve also been in the audience plenty of times, watching with sympathy as one of my cohorts agonizingly navigates this problem.
Usually, it goes something like this. They first find the [100%] tucked away in the lower left corner of the text window, and blow that up to 150 or 200 percent. Then they run their query to find that the results are still at 100%. So then they eventually find the Options dialog under the Tools menu, find the Fonts and Colors branch of the tree, and then groan when they realize they have to figure out which three or four of the 30 different fonts they need to change. Sometimes, they’ll give up there and just go use ZoomIt (which any good technical presenter should have available anyway), but constantly bouncing around with ZoomIt will get old quickly over the course of an hour-long session.
Having VSSettings files is a good idea, although I’ve noticed oddities when also trying to change colors (e.g., darker theme for regular development but a lighter theme for presentations), so test out any settings files you want to use and make sure you can swap back and forth without seeing weird behavior.
Comments closedIt’s Release Day! 🙂
New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).
New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.
It’s going to be a busy couple of days for some people…
Comments closedChris Webb shows how to log into Azure Analysis Services from Management Studio as a different user:
When Azure Analysis Services was announced I had to try it out right away. Of course I didn’t read the instructions properly so when I tried to log in to my Azure Analysis Services instance from SQL Server Management Studio, like an idiot I logged in with the wrong username. The problem is that once you’ve done this, with current versions of SQL Server Management Studio there’s no way of logging out and logging in as a different user. Luckily Igor Uzhviev of Microsoft had a solution for me and I thought I’d share it for anyone else who’s made the same mistake. Here’s what you need to do:
This seems a bit much, but should just be a temporary workaround.
Comments closedArun Sirpal found a bug in Management Studio 2016:
The actual message that you are presented states: Tables with sparse columns cannot be compressed – I have no sparse columns.
I never had this issue with SQL Server 2014 Management Studio
It looks like that bug has been fixed in the latest version of Management Studio, so if that affects you, it’s worth an upgrade.
Comments closed