Monday 30 October 2017

Calculating age using sql query

A very common requirement is to calculate aging. With the help of below SQL script, we will calculate the age very easily.
DECLARE @MTH INT,
       @Years INT,
       @Months INT,
       @Dates INT,
       @GETDays INT
DECLARE @STARTDATE AS DATETIME,
       @ENDDATE AS DATETIME

SET @STARTDATE =  format(cast('03/01/1988' as date),'yyyyMMdd')
SET @ENDDATE = GETDATE()
SET @Years = DATEDIFF(YY, @STARTDATE, @ENDDATE)
SET @Months = DATEDIFF(MM, @STARTDATE, @ENDDATE)
SET @MTH = CASE
              WHEN DATEPART(DD, @ENDDATE) <= DATEPART(DD, @STARTDATE)
                     THEN ABS(DATEDIFF(MM, DATEADD(YY, @Years, @STARTDATE), @ENDDATE)) - 1
              ELSE ABS(DATEDIFF(MM, DATEADD(YY, @Years, @STARTDATE), @ENDDATE))
              END
SET @GETDays = CASE
              WHEN DATEPART(DD, @ENDDATE) <= DATEPART(DD, @STARTDATE)
                     THEN (DATEPART(DD, @ENDDATE) + DAY(EOMONTH(DATEADD(MM, - 1, GETDATE())))) - DATEPART(DD, @STARTDATE)
              ELSE DATEPART(DD, @ENDDATE) - DATEPART(DD, @STARTDATE)
              END

SELECT CONVERT(VARCHAR(10), @Years) + ' YEAR(s) ' + CONVERT(VARCHAR(10), @MTH) + ' MONTH(s) ' + CONVERT(VARCHAR(10), @GETDays) + ' DAY(s)' Age


The input we need to give as MM/DD/YYYY format.
Here I am calculating my age. My Birth date is 03/01/1988. See the output.


 

Thursday 26 October 2017

Send email to DBA team when a database created or dropped or Altered

Our requirement is that when any DBA team member creates or Alter or dropped a mail alert trigger to Team members.
With the help of DDL trigger, we can achieve.
Read Trigger: Trigger in SQL server
Read DDL Trigger: DDL Trigger in sql server
Let’s see the example
Here I am creating a DDL trigger on the server and with the help of sp_send_dbmail SP sending the email.
USE master
GO

CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +' on ' + cast(GETDATE() as varchar(20))
SET @results =
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

--sending mail
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'Bagesh_Mail',
 @recipients = 'bageshkumarbagimsbi@gmail.com',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message

GO



Now trigger is created successfully.
Whenever we will create a database a mail trigger to 'bageshkumarbagimsbi@gmail.com'.
Let’s see. Now I am creating a database on my server
CREATE DATABASE [Test_DDL_TRIGGER]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Test_DDL_TRIGGER', FILENAME = N'D:\Test_DDL_TRIGGER.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Test_DDL_TRIGGER_log', FILENAME = N'D:\Test_DDL_TRIGGER_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO


When we created a database a mail trigger in the inbox.
See the database in the server.

Now I am seeing my Inbox

See the mail body


Writing a trigger when the database is dropped

USE master
GO

ALTER TRIGGER [ddl_trig_database_DROP]
ON ALL SERVER
FOR DROP_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'DATABASE Droped on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +' on ' + cast(GETDATE() as varchar(20)) 
SET @results =
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'DDL_Trigger_Test',
 @recipients = 'bageshkumarbagimsbi@gmail.com',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message

GO


Trigger created successfully.
Let’s drop the database.
DROP DATABASE Test_DDL_TRIGGER

See the result.

See the body


Similarly, we can write the trigger on Alter database, Create any object on the database.


Hope this will be helpful. Please leave your comment. 

Zip and unzip the files using 7-Zip in SSIS

ZIP is an archive file format that supports lossless data compression. A .ZIP file may contain one or more files or directories that may have been compressed.
We have many scenarios that we need to zip many files which we come across and then so some operations like either sending it as an email or just moving the zipped file to some other destinations etc.
 zipping or unzipping manual is a very tedious task. Here we will learn how to zip and unzip files in SSIS.

Let’s see how to zip or unzip the file using 7-Zip in SSIS

Open the SSDT.
Take Execute process task.
  
Before configuring the execute process task see the files which we are going to zip
First, we will zip one file after that we will learn to zip all files in folder
  

Here I am going to Zip Emp.txt file.
Now I am configuring the Execute process task.
  

There below properties, we need to configure
Ø  Executable
Ø  Arguments
Ø  Working Directory

Executable:

Here we need to set the exe path of 7-Zip



Select the 7z Executable exe.
Click ok.

Arguments

In Arguments, we need to give the zip command and zip file name and file name
a -t7z "F:\Emp.ZIP" "F:\Emp.txt"
a –t7z is used for zipping.

Working Directory

Where we want to zip the files
F:\


Now click ok.
Now the package is ready to run.


Before running the package.


Now I am running this package.


The package is completed successfully. See the output.


Zipped the file.


Zip all files from a folder

For zipping the files from a folder we need to change argument
  
If we give *.* then it will take files. If we to archive some specific file format then we need to give *.File extension (e.g. *.txt, *.cvs etc)
Click ok.
Package is ready to run
Before running the package.
  
Now I am running this package.

See the output

See the zip files

Unzip the files

To unzip the file, we need to change the argument.

I want to extract files on UnZip folder.
Now I am executing the package.
See the output

 

Friday 13 October 2017

No cache mode in Lookup transformation in SSIS

Read lookup transformation on below link


Here we will discuss details in No cache mode in lookup transformation and how its work.
 If we selected this option then, Lookup Transformation will not use cache to store the lookup table at any stage. When a new row comes from the data flow, the Lookup Transformation directly check in the lookup table for the matching values.
Here I am selecting No cache mode.


Now I am executing the package


In the no-cache mode, every time hit the reference dataset.
For better understanding, I am looking SQL executing in the SQL Profiler.
Read SQL Profiler here: SQL server profiler
Open the SQL Profiler
See it in the profiler
  


For the every record SQL query executed in the reference dataset. It is very slow.

Partial cache mode in lookup transformation in SSIS

Read lookup transformation on below link


Here we will discuss details in Partial Cache mode in lookup transformation and how its work.
If we selected this option then, Lookup Transformation starts the transformation with an empty cache. When a new row comes from the data flow, the Lookup Transformation first checks for the matching values in its cache. If it is not found in the cache then it will check in the lookup table. And if no match is found, it queries the lookup table. If the match is found in the lookup table then, the value will be cached (stored in the memory) for the next time. If the lookup table is very big then we can use this approach. We can set the size of the partial cache.
Here I am selecting Partial cache mode
  

When we are selecting the Partial cache mode the Advance tab comes in picture.
Now we need to select the Advance tab and set the buffer size.


By default cache size is 25 MB. We can increase or decrease the size of the cache. If we want the store the no match records in this case we need to check the Enable cache for rows with no matching entries.
If we want to write our one custom query then we need to check the Modify the SQL statement and write our own SQL script.

Data is not loaded in a cache in pre-execution phase.
See now I am executing the package.
  

See the pre executing phase data is not loaded in cache. In full cache, mode data is loaded in pre-execution phase.


Now we can see the operation in SQL server profiler.
Read SQL Profiler here: SQL server profiler
Open the SQL Profiler
  
Dynamic SQL query hit the reference database and fetch that record from reference dataset and tore that records in the cache.
Dynamic SQL query is nothing but we can see in the advance tab.
  

Question marks (?) except AddressID. See it in sql profiler.




Popular Posts