Introducing JSON for SQL Server 2016

Problem

On my current project, there is a requirement to extract data, which is stored in one database (D1) and load it into another database (D2). That should be simple, right? But, the client is not ready to give access to the D2 database due to confidential data. We have to push data to the D2 database via the REST API which accepts JSON as an input. We are trying to find an option to convert the data into JSON format at the database level. Unfortunately, SQL Server does not support converting data into JSON format. To resolve this issue, we developed a .NET utility to convert the data to JSON format with the help of the “JavaScriptSerializer” class then pushed the data to the D2 database via REST API I see SQL Server 2016 now supports JSON. Is it possible to replace our .NET utility? In this tip, we will demonstrate how use JSON in SQL Server 2016.

Solution

JSON support in SQL Server is one of the highly ranked requests in Connect #673824, which received 1000+ votes.

What is JSON?

To Continue Reading >> Please Refer My Articles at MsSQLTips.com

SQL 2016 - Skip Prompting Save

Problem

Sometime we may write a SQL script to verify the data or monitor any activities or adhoc tasks in database. In that case, We opens "N" number of Query windows and continue our activities in SSMS. We may not required to keep/save the scripts, but when we  close the windows we will get as many number of prompts for save/cancel the changes. also you will be forced to click each and every annoying save prompts.

Solution:

This Problem is resolved in SSMS 2016 release.

Microsoft has provided a new option to avoid prompting for saving unsaved files. 

To use this feature, Go to "Tools" Menu > Options > Query Execution > SQL Server > General > Un-check "Prompt to save unsaved T-SQL query windows on close"

Note:

You have to disable this option before open any query window, otherwise SSMS will prompt the save changes window at the time of closing

Warning:

Do not disable this option, if you do, you may lose valid scripts one day if you forget to save the changes when you close any script accidentally. so don't forget to enable this option after use.

Requested By:

A connect item was raised to add this feature by Erland Sommarskog, Refer 

Connect #308372, which replaced Connect #124686. It was denied by Microsoft earlier and closed the ticket stating as "Won't Fix", But due to Many People requests, Now Microsoft has introduced this feature in SQL 2016

SQL 2016 - Automatic Check for Updates

SQL SERVER 2016 - Automatic Check for Updates (New Features in SSMS)

Now you can upgrade your SQL server with latest updates via SSMS 2016 as well, This feature is introduced in SQL 2016 CTP version.

If any updates are available for SQL Server 2016, you will be notified automatically via a Taskbar notification as shown below

If you click on the notification then you can see list of updates as below in SSMS Interface

Even you can manually check the updates in SSMS by click on Tools Menu, Select "Check For Updates..." to see if there are any updates available.

SQL 2016 - Rule "Oracle JRE 7 Update 51 (64-bit) or higher is required" failed.

Problem
Today I was trying to install SQL 2016 CTP2.2 in my system. After selected all the feature, I faced the below error while validating the feature rules.

Rule: Oracle JRE 7 Update 51 (64-bit) or higher is required
Error: I clicked on the Failed Link and got the detailed error as below

Rule Check Result

Rule "Oracle JRE 7 Update 51 (64-bit) or higher is required" failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

Cause
I bit surprised why Microsoft product requires Oracle JRE!!

After little Analysis, Found that, To add the PolyBase functionality, we should have JRE version 7u51 or higher in our system

Solution 1:
If you do not require PolyBase then go back to previous page Un check the Polybase feature, then continue the installation

Solution 2:
If require Polybase then follow the below step to Download JRE

Step1: 
Go to link "http://go.microsoft.com/fwlink/?LinkId=526030" given in error page which will redirect to ("http://www.oracle.com/technetwork/java/javase/downloads/index.html")

Step2: Click on "Download" button which is under JRE (Right Side)

Step3: To Download, you must accept the Oracle Binary Code License Agreement, So Click on "Accept License Agreement"

Step4: Download JRE based on OS Architecture

Step5: Install it

Post Installation of JRE 7u51 or higher version, re-run the feature Rule validation, you will see the rule status as "Passed", then you can continue the installation.

Hope This Helps!

SQL - Get Procedure/Function/Trigger Name

Problem:
When I write any procedure for UI functionality, I may require to throw the Procedure Name to UI if any error occurs, This will help people to identify/Isolate the bug if occured
Also I Dont want to hard code the procedure name in the SPs because sometime I/someone may change the SP Name in future.
How can I prevent such a basic mistake as well as use a consistent process?

Solution:
SQL Server has a function called @@PROCID, If you use this function inside of any procedure/function/trigger, then it will return the objectid that currently running. below snippets will help you how to use this function.

Sample SP:
Create Procedure Procedure1
As
DECLARE @ProcName sysname
SET @ProcName = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) +'.'+QUOTENAME(OBJECT_NAME(@@PROCID))
SELECT @ProcName

Execute SP:

Exec Procedure1

Output:
[dbo].[Procedure1]
@@PROCID Function is available from SQL 2000 onwards. This Function return Object ID, use OBJECT_NAME Function to get the Name of the Object as mentioned in the script given above.

Next Steps:
Refer the below documentation to understand complete functionality of this function as well as how to use it in different ways

MSDN: @@PROCID (Transact-SQL)
TechNet: @@PROCID

SSIS Lookup Transformation - Handle Case Sensitive Issue

Problem:
I have a lookup transformation in one of my SSIS packages, used it to get ID of an employee record in a dimension table. However my problem is that some of the source data has employee names in Different case, which is not matching with the Case that Stored in the Dimension Table.

For Example:
Source Table Employee Name is "KUmar", but in the Dimension table, It is Stored as "Kumar"

The Lookup is failing for this Scenario wherever the case of the Employee Name are not similar

 So, How can I get rid of this issue, Is there a way to make a lookup transformation ignore case?

Solution:

There is No option to change the transformation be case-insensitive directly. But We can handle this issue in two ways:

Option 1: If the data need not to lookup based on the case Then Convert both in to similar (UPPER (Or) lower) case before do the Lookup. This will not omit any records, you will get matched records for all

Option 2: Set the CacheType property to Handle the case Issue.

Full Cache: This option is case sensitive by default.  
Partial and No-cache: This option uses the Collation setting of the database (or table) to handle case.

Set the CacheType property as Partial or None, Now row by row comparisons will be taken care by SQL Server and not by the SSIS lookup component.

 

Hope This Helps!

SQL - Get Current, Previous & Next Quarter

The below SQL script will help you to find, First & Last Date of the Current/Previous/Next Quarter based on the current Date.

 

--To get the first day of the previous quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0) As Previous_Quarter_FirstDay

 

--To get the last day of the previous quarter:

SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) As Previous_Quarter_LastDay

 

--To get the first day of the current quarter:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0) As Current_Quarter_FirstDay

 

--To get the last day of the current quarter:

SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0)) As Current_Quarter_LastDay

 

--To get the first day of the next quarter:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0) As Next_Quarter_FirstDay

 

--To get the last day of the next quarter:

SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0)) As Next_Quarter_LastDay

 

If you need to find Quarter and previous quarter for any specific date, Then Replace GETDATE() with the date.
Hope This Helps!

 

 

CTRL + R Issue in SSMS

Problem

 

I often use CTRL+R key to hide/show the result window, also use F7 & F8 keys to view the Object Explorer Details, Recently it stopped working after Microsoft windows Updates.

 

Whenever I hit "CTRL+R", The status bar says..."(Ctrl+R) was pressed. Waiting for the second key of chord..." as shown in the exhibit below

 

 

After followed below steps, the Key Functionalities are re-enabled

 

Solution

In SQL Server Management Studio,

 

1. Open the Tools menu,Options...



2. Under the Environment node click on the Keyboard node

3. Click the Reset button

4. Click Yes on the dialog that pops up

 



 This step will solve your issue. Hope This Help!

SQL Server Management Studio Query Designer Shortcuts

Problem

 

While writing and editing scripts, we often try to use the keyboard as much as possible. This should be the same for SQL Server scripts as well. When using the Query Designer in SSMS there are some shortcuts that will help you be more efficient and in this tip we will take a look at a few of these shortcuts.

 

Solution

As you know, shortcut keys play a vital role. A shortcut is a key or combination of keys assigned to perform an operation.

 

The best and familiar shortcut keys are:

• Save (CTRL + S) or (ALT + F + S)
• Copy (CTRL + C)
• Paste (CTRL + V)
• Cut (CTRL + X)
• Undo (CTRL + Z)
• Redo (CTRL + Y)

These are all familiar shortcuts keys which are common and standard across most of the modern operating systems and associated applications to perform the same activities.

 

In this tip, we will look at a couple of shortcut keys to work with the "Query Designer" editor in SSMS.

 

To Continue Reading >> Please Refer My Articles at MsSQLTips.com

Azure DB Backup/Restore Via SSMS 2014

This topic will provide details(on high level) of new features introduced in SQL Server 2014 Management Studio (SSMS) to Manage Azure Databases.

 

Enhanced Backup:SQL Server 2014 provides new Windows Azure integration to SQL Server's backup capabilities. You can specify a Windows Azure URL as the target for your SQL Server 2014 database backups. This new Windows Azure backup feature is fully integrated into SSMS.
 

In Backup Interface, you will see additional Option called "URL" as shown in the exhibit below,This option will backup the data on azure(cloud), 

 

Select "URL" as "Back up to" and provide the azure connection and file details to backup the data on azure.

 

Enhanced Restore: Same with the restores, you can choose URL as your Source…and provide the details accordingly as shown below.
 



 

Select "URL" and Click on "Add" Button

 

 

This backup and restore functionality are same or similar to when using DISK or TAPE, with a few differences.

 

Hope This Helps!

Deprecated/Discontinued DBEngine Features in SQL Server 2014

As we all aware, each and every new release of any software, we might expect changes in the release like, new features, update/upgrade on the existing features, outdated features due to compatibilities or alternate options.

 

The below link provides the list of the features and functionality that Microsoft is dropping from SQL Server 2014,

 

Deprecated Database Engine Features in SQL Server 2014

Discontinued Database Engine Functionality in SQL Server 2014

SQL Server 2016 CTP 2

Microsoft has released the first public preview of SQL Server 2016 Community Technology Preview 2. This version delivered with faster transactions and queries, deeper insights on any device, advanced analytics, new security technology, and new hybrid cloud scenarios, etc., Also find the other benefits below,

 

Benefits:

• Enhanced in-memory performance provide up to 30x faster transactions, more than 100x faster queries than disk based relational databases and real-time operational analytics
• New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes
• Built-in advanced analytics– provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database
• Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
• Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology
• Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner  without application changes
• Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

Video:

       SQL Server evolution: new innovations

 

Download SQL 2016 WhitePaper:

       Mission Critical technical white paper

       In-memory OLTP technical white paper

       In-memory OLTP and Columnstore Feature Comparison

       Deeper Insights Across Data technical white paper

       Hyper-scale Cloud technical white paper

 

To Know More about it, Please check the link below

       http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/

 

  

Visual Studio and Team Foundation Server 2015 - RC

Recently Microsoft released RC for VS2015 and TFS 2015, Refer the links below for more details:

Download Visual Studio and Team Foundation Server 2015 RC
VM of Visual Studio 2015 RC in Azure
Visual Studio 2015 RC Release notes
TFS 2015 RC Release notes
Visual Studio 2015 RC KB Article
TFS 2015 RC KB Article