Search

Advertisements

Showing posts with label SQL Server 2016. Show all posts
Showing posts with label SQL Server 2016. Show all posts

SQL Serve(R) visualization on SSRS

We all know, SQL Server R Services is one of the new feature introduced in the recent release of SQL Server. 


Below are the benefits of integrating the R language within SQL Server:

i)  keep analytics close to the data 
ii) eliminate the costs and security risks associated with data movement.


So it means, we can use the rich and powerful R language within SQL Server management studio (SSMS) to create models and generate predictions using your SQL Server data. 


SQL Server 2016 - Getting started with R Services

R Services (In-Database) provides a platform for developing and deploying intelligent applications that uncover new insights.

R Services (In-Database) integrates the R language with SQL Server, you can keep analytics close to the data and eliminate the costs and security risks associated with data movement.

To install R Services


Publish an SSIS Package result set as a SQL View

From SQL server version 2012 onwards there is an in-built mechanism to publish SSIS package result set into SQL view

1.    Requires SSIS package to be designed with Data Streaming Destination.

This destination component needs to be separately downloaded in 2012 and 2014. From 2016, it is part of standard toolbox item.

2.    Running Data Feed Publish Wizard

    i)  Creates a linked server – SSISOLEDB.

    ii) Creates a SQL view in the specified database.


Below are the steps to publish an SSIS Package result set as a SQL View

SSRS 2016 Mobile reports

In this post, I have provided quick walk-through on Integration of Mobile reports with SSRS 2016 


Below are the pre-requisites to play with mobile reports, Inside Azure VM – Install below software’s:

1.       Microsoft Visual C++ 2012 Redistributable (x86) - 11.0.61030
2.       Microsoft SQL Server Mobile Report Publisher
3.       Microsoft® SQL Server® 2016 Report Builder
4.       Configured a Firewall for Report Server Access
5.       AdventureWorks Databases and Scripts for SQL Server 2016 CTP3

SSRS 2016 - Treemap and Sunburst - New Shape Chart types

In SSRS 2016, two new Shape Chart types are included
i)  Treemap 
ii) Sunburst 

Tree Map and sunburst visualizations are great for visually representing hierarchal data.

We can add this new chart types like same way we add other charts.
Design -> Right-click on report body -> Insert -> Chart -> Shape -> Tree map / Sunburst.


Microsoft Azure - Move data from another computer to SQL Server instance on VM using SSIS

In this article, let us see on how to move data from another computer over internet to SQL Server instance on virtual machine using SSIS.

If you have no idea in creating and connecting to Azure SQL Server VM, Please refer below links :




In this post, let us also see on how to import JSON file on local computer to SQL Server on VM through this example.


SQL Server Import (Bulk load) JSON file into table

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see on how to import JSON data into SQL Server table.

Using OPENROWSET is one of the option to bulk load JSON data into SQL Server table.

SQL Server JSON - Examples - JSON_QUERY, JSON_VALUE

In this post, let us see some examples on handling JSON data in SQL Server using JSON_QUERY, JSON_VALUE

Consider below sample JSON data, let us check whether it is valid :

{"MSSQL":
      {  
 "SQL Server":{  
"Version":"6.0",
"CompatibilityLevel":60,
"CodeName":"SQL95" },
"YearofRelease":"1995"  
      }
 

}

SQL Server - Convert JSON data into relational data (rows & columns) format

In my previous post, I shared some examples on Converting relational data to JSON data in SQL Server.


In this post, let us see examples for converting JSON data into relational data format.

For Example: Consider below JSON data :

Microsoft Azure - Getting started with SSRS on VM

In this article, let us see steps to get started with SQL Server Reporting Services (Report Builder 2016) on Microsoft Azure SQL Server Virtual machine.

To create & connect to SQL Server VM, refer my previous post.


Microsoft Azure - Connect to SQL Server on VM over Internet

In this article, let us see steps to connect to SQL Server on VM over Internet.

Below are the three steps to connect to SQL Server on VM over Internet :

1) In Azure portal - Under VIRTUAL MACHINES - Create an ENDPOINT
2) In VM - Create SQL Server login and enable SQL Server Authentication mode
3) In VM - Windows Firewall with Advanced Security - Add a New Inbound rule to enable Port


SQL Server - Compare Execution Plan

In SQL Server 2016, one of the SSMS enhancement is option to compare query execution plan.

This is how we can compare execution plan in SQL Server Management Studio 2016:

SQL Server 2016 - Drop Objects IF EXISTS

In SQL Server 2016 many T-SQL enhancements were introduced. In this article, I am going to share one among them.

In SQL Server 2016 there is a new way to drop objects with simplified syntax.

In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)

IF OBJECT_ID (N'dbo.Test_tbl', N'U') IS NOT NULL
DROP TABLE dbo.Test_tbl;
GO

Below are some examples:

Microsoft Azure - How to create SQL Server Virtual machine and connect to SQL Server

In this post, let us see step by step instructions on how to create SQL Server Virtual machine and connect to SQL Server.

Steps to create SQL Server VM:


Login into azure portal and goto VIRTUAL MACHINES




SQL Server - Split CSV into separate row and other splitting techniques using OPENJSON

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see some examples on splitting comma separated string into separate rows using OPENJSON

 --Split comma separated numeric
SELECT value FROM OPENJSON('[1,2,3]')

-- Split comma separated alphabets
SELECT value FROM OPENJSON('["A","B","C"]')

--Ignore null while splitting

SQL Server - Convert relational data to JSON data


In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see examples on how to convert relational data (data in rows & columns format) to JSON format

Advertisements