Wednesday, July 29, 2015

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!

Tuesday, July 14, 2015

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