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]
GO
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.

Temporal_Table_OO

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.

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).

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.

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

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.
----------
[oledb]
; 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.

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:

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.