Management Studio has new DAX Query Window

We have been writing DAX queries using MDX Query Editor whenever we need to do something with Analysis Services Tabular Model databases (Read my old post on this: How to write SELECT for Analysis Services Tabular using DAX). Now, with this month Management Studio release (release number 17.0), we have a new Query Window for DAX.

You can download the latest at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Once the new release is installed, you should see the icon and menu item for DAX Query Window;

Writing DAX Queries

Here is an example of DAX queries that can be written. As you see, first statement uses SUMMERIZE function to produce a result that shows sales by day of the week and product. Second query creates a measure using DEFINE MEASURE and uses it again with another query.

EVALUATE 
SUMMARIZE(Sales_201501, Sales_201501[CalendarDayOfWeekLabel]
 , Sales_201501[ProductName]
 , "Sales", SUM(Sales_201501[SalesAmount]))

GO

DEFINE MEASURE Sales_201501[NewSales] = SUMX(FILTER(Sales_201501, Sales_201501[IsWorkDay]="WeekEnd")
          , Sales_201501[SalesAmount])
EVALUATE 
SUMMARIZE(Sales_201501, Sales_201501[CalendarDayOfWeekLabel]
 , Sales_201501[ProductName]
 , "Sales", Sales_201501[NewSales])

Here is the result of it;

Management Studio Quick Launch has PresentOn for turning Presentation Mode on

A new option has been added to Management Studio - Quick Launch for enabling Presentation Mode on and off. This is really useful during classes, workshops and presentations because it allows us to quickly Zoom in, enhancing the visibility of SSMS.

Read my previous post on Quick Launch: http://dinesql.blogspot.com/2017/05/what-is-quick-launch-in-management-studio.html

You can download the latest at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

The Presentation Mode is based on qlt_presentermode_default.xml file. It has pre-defined font family and size for both environment and editor. When the Presentation Mode is turned on, it uses the setting in this file and changes the way it displays.

In order to turn on or edit the file, all we need to do is, go to Quick Launch and start typing Present. When you start typing, it shows items related to it.

If I select PresentEdit, it will open the xml file and allows us to edit it. If we select PresentOn, then it will set the environment as per the configuration in the xml file.

To revert back to default setting, type Restore... to get RestoreDefaultFonts item and select it.

What is Quick Launch in Management Studio?

Have you noticed an input box called Quick Launch at top-right corner of the Management Studio? Have you used it? Do you know that why it has been given?

This input box allows us to search IDE specific options, templates and menus and open them without going through standard menu items. For example, if I want to change the font and size of the font, I have to go through Tools -> Options -> Environment -> Font and Colors. But Quick Launch allows us to open it quickly without going through menu items.

If you click on it (Shortcut is Ctrl + Q), and type @, then it shows possible items that can be searched;

If you type @opt, then it allows use to search a specific menu under options;

Once searched, it displays the result. The required item can be opened by clicking it;

As you see, this can be used for opening many items related to IDE without going through multiple steps.

You can read more on this at: https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(vs.quicklaunch)&rd=true

What if I create an Azure SQL Database using Management Studio (SSMS)?

Will there be differences if I create my Azure SQL Database using Management Studio instead of Azure Portal?

Yes, there are. One major difference I noticed is, Snapshot Isolation. By default it is on in an Azure SQL Database along with Is Read Committed Snapshot On;

[Database created using the portal]

However, if you create a database using SSMS, it does not set as expected;

[Database created using SSMS]

As you see, even though Snapshot Isolation is on, Is Read Committed Snapshot On is set as false. Not sure how it is happening, have already asked from experts, will update this with reasons once I received a reply.

SQL Server - Adding IF, BEGIN/END, WHILE statement easily to the code

Do you know that you can add, specifically surround your code with BEGIN/END, IF, and WHILE statements using a menu item without writing them? Yes, with Management Studio, it is possible. Here are the steps for doing it;

Assume that you have the below code that needs to be repeated based on a condition;

All you have to do is, select the code needs to be surrounded and press Ctrl+K and Ctrl+S (Just press Ctrl and hold, and press K first and then S). This is what happen when you do it;

Select WHILE and press Tab key. This is what you should see;

Set the condition as you need now. This shortcut can be opened using the context menu as well.

Management Studio Error: The Visual Studio component cache is out of date. Please restart Visual Studio.

I experienced this error when opening Management Studio just after the installation of Windows update.

Not sure about the reason but I got a similar error when opening Visual Studio as well. I could not find the exact reason for this and closing and re-opening did not help either. I just tried with killing all Visual Studio related tasks using Task Manager and then tried, yes, it worked.

Not sure whether you faced this with Management Studio before. If someone has more details on this specifically to SQL Server, please share.

Inserting NULL to cells when editing rows

This is not something new but many are unaware this. If you want to insert a NULL to one of the cells when editing records using Management Studio Edit, what is the short-cut key can be used?

If you need to set NULL to a cell, you can press CTRL+0 for adding NULL.

In addition to that, you can use following combinations during Edit-Mode for opening relevant windows.

SQL Server Backup to URL - Now easier than before

I made a post on SQL Server Backup to URL that describes how to take a backup to URL. Earlier, this needs the credential created before you take a backup but now, creating credentials using Policy on the container and Shared Access Signature Key (see more this with SQL Server On-Premise Database files maintained in Azure) can be done with the given GUI. This saves time and not only that, it clears the confusion had with previous GUI.

The new GUI is come with SQL Server Management Studio July Preview.

This is what we had before;

Now the interface with latest Management Studio;

If you have already created a credential, it can be picked from the first drop-down. If not, click on New container button and Sign in to your Azure Account. Once signed in, select the Storage and Container.

You can generate the Shared Access Signature by clicking Create Credential button, and complete it.

Now you can select the Azure storage container  and take the back.

SQL Server Management Studio July 2016 is available

Good thing is we get continuous releases for Management Studio. Microsoft has released the newest version of SQL Server Management Studio which is July 2016 release. Latest version is 13.0.15500. The June 2016 release was 13.0.15000.

You can download the latest from here.

Some of the major changes are as follows (quoted from the same page);

• Support for Azure SQL Data Warehouse in SSMS.
• Significant updates to the SQL Server PowerShell module. This includes a new SQL PowerShell module and new CMDLETs for Always Encrypted, SQL Agent, and SQL Error Logs.
• Support for PowerShell script generation in the Always Encrypted wizard.
• Significantly improved connection times to Azure SQL databases.
• New ‘Backup to URL’ dialog to support the creation of Azure storage credentials for SQL Server 2016 database backups. This provides a more streamlined experience for storing database backups in an Azure storage account.
• New Restore dialog to streamline restoring a SQL Server 2016 database backup from the Microsoft Azure storage service.

SQL Server Query Editor with Query Window

Some prefer write the TSQL statement without a help from an editor but some prefer to use an editor because it simply allows us to select tables and columns easily and make joins, grouping and filtering easily. I personally prefer to write statements by myself as it helps me to remember the way of writing it.

Those who like to get a help from editor for writing SELECT statements, they go to the View node in Object Explorer and use New View menu item for opening the editor. It allows us to construct the SELECT statement without writing it and the statement can be used without creating the view. However, there is a way of opening the editor with New Query Window without opening it with Create View.

Do you know that there is a menu item called Design Query in Editor... in Query menu? This menu item opens the editor that can be used for constructing the query and adding to your New Query window.

Although this is not a new menu item, many do not use it, in fact many do not know that it is available. It is usefull, you can get your statement written without spending much time.

Browsing internet inside SQL Server Management Studio

How often you google or bing on technical issues while developing modules with SQL Server? For me, it is quite often as I always do searches on codes I write. Now the question is, do you go out from your Management Studio for browsing internet?

Do you know that you can browse internet inside the SSMS? Yes, it is possible and it is not something newly added with SQL Server 2016. It was available even with SQL Server 2012 :).

This is really simple. You have been given an icon in the toolbar for opening an interface for browsing and then you can work with it as you want.

In addition to that, we have been given two shortcuts: Community Projects and Samples and Resource Center that help you to get more info on SQL Server whenever wanted.

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Once I wrote a post on how to take a copy of Azure SQL Database into on-premise server: Taking backups of Azure SQL Database and restoring to on-premise server. It was a simple method, all we have to do is, export the database into a bacpac file and add to the server using Import Data-tier Application... 

With new operating system installation, I was trying to do the same but was hit by an error;

Could not load schema model from package. (Microsoft.SqlServer.Dac)

Internal Error. The database platform service with type

Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid.....

I immediately realized the issue, it was all about the version of Management Studio. I tried the same with Management Studio April Preview and I was able to add the database exported from Azure.

As per the reading I did, if you try with Management Studio that comes with SQL Server 2014, you need Service Pack 1 and CU 5 or above.

SQL Server 2016 New Three Releases: SQL Server RC 3, Management Studio, SQL Server Data Tools

Microsoft SQL Server team has announced three new releases related to SQL Server product suite;

1. SQL Server Release Candidate 3
   This is the last Release Candidate and it is available for downloading now.
   Click here to download it and click here to see the release note.
   
2. SQL Server Management Studio April Preview
   New Preview is available with bug fixes and some enhancements. Click here to read more and click here to download it.
   
3. SQL Server Data Tools Preview Update
   An update has been released for SQL Server Data Tools specifically for SQL Server Release Candidate 3. Click here to download the Preview.

Query Shortcuts in Management Studio

The most friendly database tool, Management Studio was released with SQL Server 2005 and it has become the right-hand for both database administrators and developers. SQL Server Management Studio is an integrated environment for doing all types of operations and management related to SQL Server and it is a rich tool equipped with many functionalities and an editor. Although we have been using it for long time, Query Shortcuts are still unknown to many. Here is a post on it, and you will surely find the usefulness of them.

Query Shortcuts allows you to configure key combinations for executing your common commands. By default, three shortcuts are already configured and can be used immediately. Seeing configured shortcuts and new shortcut configuration can be done with Options interface which can be opened with Tools menu.

As you see, three shortcuts are already configured. If you press Ctrl+1 in Query Editor, sp_who procedure will be automatically executed and you will see the result of it.

In addition to that, we can configure our own short cuts. Here are some example;

"SELECT * FROM " is configured with Ctrl+5 and "EXEC" is configured with Ctrl+6. This allows us to highlight either a table name or stored procedure and get it either queried or executed using assigned shortcut. For example, if Person.Person is selected and Ctrl+5 is pressed, the statement SELECT * FROM Person.Person will executed.

Make sure you do not assign shortcuts for all operations. Operations like delete should not be configured because you can accidentally delete records once configured.

Firewall Settings for Azure SQL Server is available with Management Studio now

If you are working with Azure SQL Databases, you are well aware that Azure SQL Server needs your IP address for allowing you to access the Azure SQL database. Although it is considered as a one-time-setting, it is not if the IP address of your machine gets frequently changed or dynamically assigned from a service like DHCP. In a situation like that, we have to always open the portal and add the IP address via Firewall Settings of Azure SQL Server which is time-consuming and inconvenient.

Good news! Now this setting is available with Management Studio and if it detects that the current IP address is no longer a valid one for accessing, it allows you to login to Azure Subscription and add the current IP address to the Firewall Settings. This is really a good facility and saves time. Remember, it is available only with Management Studio 2015 December Preview.

You can download it from: https://msdn.microsoft.com/en-us/library/mt238290.aspx

"Failed to retrieve data for this request" "unknown property". (Microsoft.SqlServer.Management.Sdk.Sfc) : Microsoft SQL Server Management Studio

Are you getting this error when trying to retrieve stored procedures added to Azure V12 SQL database through Management Studio November 2015 Preview?

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------

ADDITIONAL INFORMATION:

unknown property IsNativelyCompiled (Microsoft.SqlServer.Management.Sdk.Sfc)

I think that this is only related to SSMS November 2015 Preview because there is no issue with SQL Server 2012 Management Studio. However, if you want to use the latest, install December Preview 2015, it sorts out the issue.

With SSMS November Preview

With SQL Server 2012

With SSMS December 2015 Preview

SQL Server Management Studio - December 2015 Release

The SQL Server Management Studio - December 2015 release is available for downloading with few enhancements related to November release and some bug fixes. Once installed, you should see the version upgrade from 12.0.2269.0 to 13.0.900.73. Here is the link for downloading;

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Here are changes done (quoted from the downloading page)

• Improvements to Execution plan (Showplan) comparison - to enable comparison of the current query execution plan with one saved in a file.
• Improved IntelliSense support - for inline columnstore indexes in SSMS.
• Bug fix in Extended Events session wizard - to enable selection of templates when connected to an Azure V12 server.
• Improvements and bug fixes- to enable functionality to switch to results tab after query execution, and to display un-truncated column headers when SSMS is set to display results in grid format.

SQL Server Management Studio - October 2015 Release

Have you tried out latest SQL Server Management Studio? It is available as the October release. It can be used with SQL Server 2016 through SQL Server 2005 and supports side-by-side installation.

You can download it from here.

Here are some of the features/functionalities added with this release (from MSDN)

• New modernized lightweight web installer
• New Always Encrypted column encryption wizard 
• New Column Master Key (CMK) rotation dialog
• New stretch database monitor 
• 
  

SQL Server Management Studio - September 2015 Release

The latest Preview of Microsoft SQL Server Management Studio is release and it can be downloaded from;

https://msdn.microsoft.com/en-us/library/mt238290.aspx 

Here is the list that contains all updates for last three months (quoted directly from the site)

• SSMS August 2015 Preview
• Object explorer updates to reduce loading delay when there are a large number of databases.
• Improvements for installation of SSMS on Windows 10 machines.
• Bug fixes and updates to SQL Server Configuration manager, and SSMS user reports user interface
• SSMS July 2015 Preview
• Database diagrams for Azure SQL Database (V12).
• Improved IntelliSense support for new temporal table syntax.
• Updated DacFx library to support latest Azure SQL databases features including row-level security and Azure Active directory authentication.
• Bug fixes (updated 'check for update' UI, UI fix in 'compatibility level' list and more).
• SSMS June 2015 Preview
• New SSMS light-weight web installer.
• Automatic check for updates.
• SSMS now has full-text-search support for Azure SQL Database (V12).
• Top customer requests addressed:
• 'Edit Top 200 Rows' enabled for tables and views in Object Explorer.
• Table designer enabled for Azure SQL Database (V12).
• Database and table property dialogs enabled for Azure SQL Database (V12).
• New option to skip prompt to save T-SQL files.
• Import/Export wizard support for new Azure SQL Database service tiers (Basic, Standard, Premium).
• Numerous bug fixes (scripting scenarios, enabling change tracking for SQL databases, and more).

SQL Server Management Studio 2015 June - Preview is available for downloading

Microsoft SQL Server Management Studio 2015 June Preview is available for downloading. This comes as a separate installation, not as a part of standard SQL Server installation. I believe that the whole idea of this is, making Management Studio as a standalone tool for managing all SQL products including Azure databases.

As per MSDN, enhancements made are;

• New SSMS Installer - SSMS can now be installed with a light weight stand-alone web installer.
• SSMS Updates - Receive notification within SSMS when new updates are available, and choose to install them at your convenience.
• Enhanced SSMS support for Azure SQL Database – Several fixes and enhancements, including expanded SQL Server Management Objects (SMO) coverage, and an updated Import/Export wizard.

Here is the page for downloading: https://msdn.microsoft.com/en-us/library/mt238290.aspx

Installation is very simple and lightweight. Just as SQL Server 2016, it is recommended to install this in a separate machine as this is still a Preview.