Sunday, December 17, 2017

Connect to SQL Server 2016 using Microsoft SQL Operations Studio

It is so easy to connect to SQL Server database.

Read here: What is Microsoft SQL Operations Studio?

Read here: Microsoft SQL Operations Studio User Interface

Watch this video: Connecting to SQL Server 2016 Developer edition database using
Microsoft SQL Operations Studio

Tuesday, December 12, 2017

Run a simple query in Microsoft Operations Studio

In order to run a query in Microsoft Operations Studio you must first to connect to a database on an instance of SQL Server.

It is so easy to connect to SQL Server database.

Step 1: Understand what is meant by Microsoft Operations Studio
Read here: What is Microsoft SQL Operations Studio?

Step 2: Get a handle on using this new Interface

Read here: Microsoft SQL Operations Studio User Interface

Step 3: Learn how you may connect to SQL Server (In this example SQL Server 2016 Developer)

Watch this video: Connecting to SQL Server 2016 Developer edition database using
Microsoft SQL Operations Studio

Step 4: Running a query on SQL Server after connecting to Northwind database

Now that you are connected, you can access all the tables, views, etc as shown.

 For any table you have three options Select top 1000, edit or script table to create as shown.

When click on New Query icon in the Microsoft Operations Studio, the query plane is open as shown.

 You will be required to enter a query starting at Line 1. When you type SEL, the Select statement begins.

 You do not get the columns of employees as it is not yet even mentioned. Type in the rest of the query. Click Run. The results pane opens below the Select statement as shown.

 The Results pane has a few controls on the right. This is cool. Now you can export the results to CSV, JSON or EXCEL. You could also view it as a chart.

Perhaps, SSMS should have a view of viewing data as chart.

In summary, it is easy to connect to SQL Server and run queries. It should be noted that there is no syntax check for queries as well as intellisense.

Tuesday, December 5, 2017

SQL Operations Studio User Interface

In order to use a program, it is first of all necessary to have a clear understanding of the User Interface for screen driven application.

SQLOPS when invoked brings up the user interface screen. It is here you do most of your development.

As mentioned in previous posts this is a light-weight tool. It provides a much easier interface then the SQL Server Management Studio, a heavy-weight option.

You invoke the program by double clicking the sqlops.exe in the folder where you saved the application files.

The following window is displayed:

The following icon joins the taskbar in Windows 10.

The UI is simple and the main menu items opens the following sub-menus.
The File menu item has the query related and has access to folder/file sysyem

 The Edit meu item is like any other Microsoft application (Word, Excel, ...)

The View menu item has many more important items such as Servers, Explorer,

The Help menu is important as it is here you get he main documentation as well as initial knowledge for you to start using it.

The icons in the panel on the left gives access to the tasks and the keyboard key shortcuts.

I think even non-DBA types can use this interface. It is cool!!

SQL Operations Studio - Light-weight database management and development tool

Well there is a heavy-weight tool, SQL Server Management Studio that is installed as a separate application from SQL Server installation (used to be a part of the SQL Server installation).

SQL Operations Studio is a lighter tool. It is free and you can carry out database development operations using it. It is available for SQL Server on Windows, Linux and Docker, Azure SQL Database and Azure SQL Data warehouse on Windows, Mac or Linux.
The preview version became available in November of 2017.

If you would like to use it you need to download the version you want from here:

I downloaded the Windows version. You need to extract the files into a folder. 

The executable is sqlops.exe and it is indeed a small file.

SQLOPS user interface in Windows 10

Monday, December 4, 2017

Installing SQL Server 2017 Developer on Windows 10

This time around it was a plesant surprise. Within a couple of clicks the default instance of SQL Server 2017 Developer is installed!!

Of course you may need to install the various other features later.

Your installation begins here by double clicking the downloaded executable.

Double click the executable. Select the installation type. Of course, the Basic has been chosen here.

 Agree to the License terms by clicking Accept.

 Specify the install location. Here the default is accepted.

 Downloading of installation package beings. This will take some time which depends on your connection to Internet besides others.

 Still downloading the package....

Voila!  It is installed.

You could look at a few things on the above screen. I tried to connect by clicking Connect Now at the bottom and here is a response from SQLCMD.

However when I clicked the SQL Server Install Log folder, the screen froze and I had to reboot. The last part is my experience and yours may be different. Perhaps it does not work well with Build 17025.rs_prerelease Insider Preview version of Windows.

Here is the default installation in SSMS 2017 object explorer:

Sunday, December 3, 2017

Download SQL Server 2017 Developer Edition and test drive

You may download SQL Server 2017 versions from here.

Download link:

Three options are available as follows:

Trial (time limited enterprise edition)
SQL express




Microsoft describes this product as follows:
Industry-leading performance
Take advantage of breakthrough scalability, performance, and availability for mission-critical, intelligent applications and data warehouses.
Least vulnerable
Enhance security with encryption at rest and in motion. SQL Server is the least vulnerable database over the last 7 years in the NIST vulnerabilities database.
Real-time intelligence
Reason over any data and gain transformative insights for your business with real time analytics at up to 1M predictions/second.
End-to-end mobile BI
Transform data into actionable insights. Deliver insights on any device online or offline at one-fifth the cost of other self-service solutions.
Any application, anywhere
Build modern applications using any data, any language--on-premises and in the cloud, now on Windows, Linux, and Docker containers.

English, German, Spanish, Japanese, Italian, French, Russian, Chinese Simplified, Chinese Traditional, Portuguese Brazil, Korean

SQL Server 2017 | 64-bit ISO for Windows
SQL Server 2017 | 64-bit CAB for Windows
Other Distributions:
Windows Container Image on Docker Hub:
Linux: SQL Server 2017 on Linux and Windows page.

Tuesday, November 28, 2017

Creating a default System Versioned Temporal Table

Let us take the example of tracking the age and height of students who spend a couple of years and are montiored periodically. We can track the montiored parameters using a temporal table.
Creating a temporal table with default history table is convenient option for creating a history table with default configuration.

The Important keyword is System_Versioning. It has two values, ON or OFF. When you set it to ON, the default history table will be created.
The following code creates a temporal table with hsitory table explicitly defined.
USE [Aloha]
CREATE TABLE [dbo].[StudentMonitor]
 StudentID int Not Null Primary Key Clustered,
 LastName varchar(25) Not NULL,
 Age float NULL,
 Height float NULL,
        SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
        SysEndTime datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
        Period for SYSTEM_TIME(SysStartTime,SysEndtime)  

With (SYSTEM_VERSIONING=ON (History_table=dbo.StudentMonitor_History))

In the table that is generated the SysStartTime and SysEndTime will be hidden. The period of time is determined by Start Time and end Time.

The highlighted items in the code are required to create the temporal table.

When this statement is run in a query window, the System Versioned table (dbo.StudentMonitor(System-Versioned))  as well as the history table(dbo.StudentMonitor_History)) will be created as shown.


Monday, November 20, 2017

Report Builder reinstall does not help

Report builder was completely removed. Computer restarted and Report Builder was installed again. The previous install had upgraded a slightly earlier (probably that came with SQL Server 2014) and I had assumed that might have been the problem.

Report Builder UIs still have the same problem.

The computer is running Windows 10 Professional with this version of OS:

This following file version works and the report builder's Expression Builder has all the controls.

Sunday, November 19, 2017

Binary images in tables and Power BI

I have tried couple of times to display a table with a column containing images (binary) such as those you find in the Employees and Categories table in Northwind database. Power BI does not seem to support binary files.

For Power BI to claim superiority over competitor products Microsoft should come up with a fix to do it easily (not some programmatic trick). After all the strength of Power BI is its ease of use.

In my books on Reporting Services I did manage to display using a complicated expression using the expression builder. I am not sure whether such an approach would work in Power BI. I will try it sometime.

Images from Categories table in a Report Builder authored report:

Report Builder 3 has UI problems that needs addressing

User Interface is very important to a graphical tool such as Report Builder. I have been using since the earliest version.

This version of Report Builder 14.0.609.142 seems to have UI problems. It is not related to resolution and I think it is in need of rework.

Here how the UI to create a connection to a SQL Server using Report Builder 3. Here you can see the whole UI (all buttons), the intermediate elements are messed up.

Expression is often used in Report Builder to fashion some relevant expression. In Report Builder 3 version 14.0.609.142, there is no OK button to submit your expression to be processed. Also the window is inflexible, bad design. Even the Help button is only partially visible.

This is not related to resolution as I have tried to see how it looks at different resolutions and even in Landscape/Portrait views. If the OK and Cancel buttons did exist they would have been aligned with the half-showing Help button.

Few more images at different display settings:

Here is how it should have appeared (copy from an earlier version article on MSDN).

Saturday, November 18, 2017

Report Builder 3 expression window has no OK button

Expression is often used in Report Builder to fashion some relevant expression. In Report Builder 3 version 14.0.609.142, there is no OK button to submit your expression to be processed. Also the window is inflexible, bad design. Even the Help button is only partially visible.

Wednesday, November 15, 2017

New Subscription or New History Snapshot produces error

SQL Server 2016 SP1 on Windows 10 Professional on a Dell Laptop
Report Manager (Web Portal URL) has reports embedded, folders etc. Report server; Report Manager and Report Builder 3 are all working.
Trying to create a New Subscription or New History Snapshot produces error. Report user is administrator of SQL Server 2016 SP1 as well as installer. The current user is also the Reporting Services Administrator. The report is neither a linked report not does it have parameters. Data Source credentials are that the report author who is also the current user as well as the server installer on the Windows 10 Pro laptop.
Here are the messages. Some MSDN content on MSDN site related to this subject is outdated and does not relate to some of the procedures mentioned in MSDN articles.

Jayaram Krishnaswamy

Wednesday, November 8, 2017

Power BI and Microsoft OLEDB Provider for ODBC Drivers

Power BI does not support the Microsoft OLEDB Provider for ODBC Drivers also known as MSDASQL.

Power BI uses the .NET Framework Data Provider of OLEDB (System.Data.OLEDB).
This was found while connecting Power BI to Microsoft Excel using the connection string developed using a (Universal Data Link) UDL file.
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel7_11;DBQ=C:\Users\Owner\Desktop\Blog2017\MSSS2017\Hindu_Islam_old.xls;DefaultDir=C:\Users\Owner\Desktop\Blog2017\MSSS2017;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;";Initial Catalog=C:\Users\Owner\Desktop\Blog2017\MSSS2017\Hindu_Islam.xlsx
Also Power BI does not support the Persist Security Info attribute set to false.
This is the error message about OLE DB Provider support.

Tuesday, November 7, 2017

You do not see Report Builder 3 in your All programs

You may not the only one. I installed Report Builder 3 soon after installing SQL Server 2016 and later upgraded with SP1. I could see the Report Builder app icon in the All programs.

After installing Visual Studio Community 2017 and or after installing SQL Server Management Studio V 17.3, I do not see the Report Builder icon.

Right now it is under a different program group as shown here:

Wednesday, November 1, 2017

Problem with Microsoft Edge 41.16299.15.0.

It does not bring up the Report Server Web Services URL which is healthy viewing from other sources (Chrome, IE 11).

It also has some crazy display problems. Suddenly the page is only partially displayed. It does not matter even if you maximize the web browser display.

This is the Edge display.

The Task Manager is shown that many Edge tabs are open (but not seen in the above).

I need to use Task Manager to close the Edge.

This version was installed recently by Microsoft staff at Ala Moana to fix some computer problems.

SQL Server 2016 SP1 repair does not fix Reporting Services Problem

Problem with SQL Server Reporting Services 2016 SP1 is still unsolved. Two things that prevent me from going further is that I cannot use the SQL Server Reporting Services 2016 SP1 because:

Microsoft Edge is not displaying the Report Server.
Web Portal URL is not working and this happens to be the front-end for the Reporting Services.

I assumed that probably there is something wrong with the SQL Server 2016 Sp1 and I went through a repair process:

It appears there is no problem with the Reporting Services 2016.