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

Saturday, July 11, 2015

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!

Thursday, July 9, 2015

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!
 
 

Wednesday, July 8, 2015

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!