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.

 

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

http://bageshkumarbagi-msbi.blogspot.in/2017/08/trigger-in-sql-server.html

Read DDL Trigger: DDL Trigger in sql server

http://bageshkumarbagi-msbi.blogspot.in/2017/08/ddl-trigger-in-sql-server.html

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

Read How to: Configure Database mail in SQL server

http://bageshkumarbagi-msbi.blogspot.in/2015/10/configure-database-mail-in-sql-server.html

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

 

No cache mode in Lookup transformation in SSIS

Read lookup transformation on below link

Lookup transformation in SSIS

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

http://bageshkumarbagi-msbi.blogspot.in/2017/07/sql-server-profiler.html

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

Lookup transformation in SSIS

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

http://bageshkumarbagi-msbi.blogspot.in/2017/07/sql-server-profiler.html

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.