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!