Showing posts with label SQL Yoga. Show all posts
Showing posts with label SQL Yoga. Show all posts

June 1, 2015

SQL Server, avoid encoding special character when using FOR XML PATH

From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement

Today, we face an issue when special character gets encoded and we hate to have special character in my string. 

e.g: Table has records like: 
1. Gynecology & Obstetrics 
2. Dermatology 

Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology. 

When we used FOR XML PATH, we got the output as: 
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")

This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as: 

        SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
        FROM Table1 T2
        INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
        FOR XML PATH(''),TYPE).value('.[1]', 'varchar(max)')
    ), 1, 1, '')

After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology 

Reference : Tejas Shah (

April 4, 2015

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:
SELECT As DatabaseName, As FileName,
        m.physical_name As PhysicalFileName,
        (m.size * 8.0) / 1024 / 1024 As SizeInGB
FROM sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id
WHERE m.Type = 1
It will give us the list as follows:
Reference: Tejas Shah (

June 14, 2014

SQL Yoga : Certificate Expired, Mirroring Stopped

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows:

Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT:]

In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations.
1. Create a new certificate with longer endpoint (on Principal):
  1: -- Create a new certificate for the endpoint
  2: USE master;
  3: CREATE CERTIFICATE [Principal_Certificate_New]
  4:    WITH SUBJECT = 'Principal Certificate',
  5:     START_DATE='01/01/2014', -- Provide date prior to current date
  6:     EXPIRY_DATE='12/31/2020'; -- Provide this as future date
  7: GO

2. Take backup of the newly created certificate (on Principal):
  1: USE master;
  2: BACKUP CERTIFICATE [Principal_Certificate_New] TO FILE = N'F:\Backup\Principal_Certificate_New.cer';
  3: GO

3. Set mirroring to use the newly created certificate (On Principal):
  1: ALTER ENDPOINT DBMirrorEndPoint
  3: GO

4. Delete the old certificate for endpoint (on Principal):
  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

5. Drop the Old Certificate for Principal Login (on Mirror):
  1: USE master;
  2: DROP CERTIFICATE [Principal_Certificate_Old]
  3: GO

6. Create a new certificate from the principal backup file (on Mirror):
  1: USE master;
  2: CREATE CERTIFICATE [Principal_Certificate_New] AUTHORIZATION PrincipalServerUser
  3: FROM FILE = N'F:\Backup\Principal_Certificate_New.cer';
  4: GO

7. Create a new certificate with longer endpoint (on Mirror):
  1: use master;
  2: CREATE CERTIFICATE Mirror_Certificate_New
  3: WITH SUBJECT = 'Mirror Certificate New'
  4:  ,EXPIRY_DATE = '12/31/2020' -- Provide this as future date
  5: GO

8. Take backup of newly created certificate (on Mirror):
  1: USE master;
  2: BACKUP CERTIFICATE [mirror_new_cert] TO FILE = 'F:\Backup\Mirror_Certificate_New.cer';
  3: GO

9. Set mirroring for newly created certificate (on Mirror):
  1: USE master;
  2: ALTER ENDPOINT DBMirrorEndPoint
  4: GO

10. Drop the old certificate for endpoint (On Mirror):
  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

11. Drop the old certificate for Mirror login (On Principal):
  1: USE master;
  2: DROP CERTIFICATE [Mirror_Certificate_Old]
  3: GO

12. Create a new certificate from the mirror backup file (On Principal):
  1: USE master;
  2: CREATE CERTIFICATE [Mirror_Certificate_New] AUTHORIZATION MirrorServerUser
  3: FROM FILE = 'F:\Backup\Mirror_Certificate_New.cer'
  4: GO

13. Resume the mirroring session for each database(On Principal and Mirror):
  1: USE master;
  3: GO

By following the mentioned steps, we can resolve the certificate issues for Mirroring database and Mirroring will be resumed.
Note : 1. Always prefer to provide the endpoint date as far date, so that issue doesn’t occur very soon. 2. During this process, Primary database is available without any interruption.
Reference: Tejas Shah (

April 17, 2014

SQL SERVER: SSIS – Merge Join Transformation

Now, let’s have a look at functionality of Merge Join Transformation task in SSIS.

Benefit of using Merge join is, input datasets can be combination of any two datasets from (Excel file, XML file, OLEDB table, Flat file).Output can be result of INNER, LEFT Outer, or FULL Outer Join on both the datasets.

Merge Join Transformation has two inputs and one output. It does not support an error output.

Use of Merge Join Transformation:

Merge Join is a two-step process. First step is to sort both the input datasets(tables) in the same order, and the second step is apply merge join on the common key.Here rows from both the sorted inputs get matched together.

To Understand Merge Join Transformation in better way, lets take an example with various configuration parameters in SSIS.

1. Create sample tables:

Now we will create input tables named “Department” and “Employee” in Test database.

  1: CREATE TABLE Department
  2: (
  3: 	Dept_No INT
  4: 	,Dept_Name VARCHAR(50)
  5: 	,Location VARCHAR(50) 
  7: )
  9: INSERT INTO Department VALUES (10, 'ACCOUNTING', 'Mumbai')
 10: INSERT INTO Department VALUES (20, 'RESEARCH',   'Delhi')
 11: INSERT INTO Department VALUES (30, 'SALES',      'Mexico')
 12: INSERT INTO Department VALUES (40, 'OPERATIONS', 'Sydney')
 13: GO
 15: CREATE TABLE Employee
 16: (	
 17: 	Emp_No INT NOT NULL
 18: 	,Emp_Name VARCHAR(100)
 19: 	,Designation VARCHAR(50)
 20: 	,Manager INT
 22: 	,Salary INT
 23:     ,Dept_No INT
 24:     CONSTRAINT PK_Employee PRIMARY KEY (Emp_No)
 25:     ,CONSTRAINT FK_Dept_No FOREIGN KEY (Dept_No) REFERENCES Department(Dept_No)
 26: )
 28: INSERT INTO Employee
 29: 		(Emp_No,Emp_Name,Designation,Manager,Salary,Dept_No)
 30:  VALUES
 31:     (101, 'Tejas', 'MANAGER', 104, 4000, 20)
 32:     ,(102, 'Michel', 'ANALYST', 101, 1600,  30)
 33:     ,(103, 'Mark', 'DEVELOPER',102, 1250,  30)
 34:     ,(104, 'James', 'DIRECTOR',106, 2975,  10)
 35:     ,(105, 'Raj', 'ANALYST',7566, 3000,  20)
 36:     ,(106, 'TechnoBrains', 'PRESIDENT', NULL, 5000, 40)
 37: GO

2. Create Data Source Connection:

Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection.

3. Select Input Data Sources:

Select two different Data Sources which you need to perform merge join on as “OLE_SRC_Employee” and “OLE_SRC_Department”. Create a new “OLEDB Connection” to map it to the source datasets.

SQL Yoga - Merge Join Transformation #1

4. OLEDB Source Editor:

Now double click on “OLEDB Source”, it will open “OLEDB Source Editor” in that provide table configuration parameters and columns mapping from “Columns” tab.

5. Data Sorting:

As the Merge Join Transformation accepts the sorted data as input, we will add the sort transformation in the flow. If you know that the data is already sorted then you can set “isSorted” Property as “True” in the “Advanced Editor” for OLEDB Source of the respective dataset. Or else you can use the Sort Transformation task from “Data Flow” Transformation.

Now we need to add two Sort components and join the green arrow pipeline from “Employee” to one of the sort transformation and other pipeline from “Department” to the other Sort Transformation.

SQL Yoga - Merge Join Transformation #2

6. Sort Transformation Editor Source 1:

In order to get sorted data, Double click on the “Sort Transformation” that we have connected to “Employee” Dataset to provide the key on which you want to perform sort so that data gets re-ordered in sorted form based on the keys provided. Provide the Sort type as well as sort order if there are multiple keys on which Sort operation will work.

SQL Yoga - Merge Join Transformation #3

7. Sort Transformation  Editor Source 2:

Now we have “Employee” table data in sorted form, in the same way need to configure the sort transformation for Source 2 “Department”.

For the same double click on the “Sort Transformation” which is connected to “Department” dataset, to provide the Sort key and order in which you want to perform the sort in “Sort Type” property in Editor. Please keep in mind that the Sort type for both the source needs to be of the same type. i.e. any one of ascending or descending order.

SQL Yoga - Merge Join Transformation #4

8. Merge Join Task Component:

Now we will add Merge Join Transformation, so that we can join both the sources together.Drag the pipeline from Employee sort to Merge Join. In “Input Output Selection” popup select Output as “Sort Output” and Input as “Merge Join Left Input”. In Input user has two options as

  1. Merge Join Left Input

  2. Merge Join Right Input

Using this two options user can specify whether the input needs to be considered as left or right side dataset result.

SQL Yoga - Merge Join Transformation #5

Now you need to drag the pipeline from other “Sort transformation” and connect it to “Merge Join Transformation” as second input. While connecting the second input to the Merge Join, it will not ask for the input type as you have already provided it for the first pipeline, so by default it will select the other type of input to the Merge Join. i.e. Left or Right accordingly.

SQL Yoga - Merge Join Transformation #6

9. Merge Join Transformation Editor:

In order to configure merge join double click on the “Merge Join Transformation” to open the Editor.You need to provide the Join Type to specify which type of join operation you want to perform on the selected dataset.

Different Join types are:

  1. Inner Join
  2. Left Outer Join
  3. Full Outer Join

Here we will select the “Inner Join” as Join Type as we need to display data from both the datasets. Select “Dept_No” as Join Key as it is the common field on which we can merge two datasets data.

SQL Yoga - Merge Join Transformation #7

10. Result table creation:

We need to create a table to store the output result into Test database as per the script provided.

  1: CREATE TABLE [Merge_Join_Output] 
  2: (
  3:     [Emp_No] INT,
  4:     [Emp_Name] VARCHAR(100),
  5:     [Designation] VARCHAR(50),
  6:     [Manager] INT,
  7:     [JoinDate] DATE,
  8:     [Salary] INT,
  9:     [Dept_No] INT,
 10:     [Dept_Name] VARCHAR(50),
 11:     [Work_Location] VARCHAR(50)
 12: )
 13: GO

 11. Select “OLEDB Destination Editor” to redirect your output to the “Merge_Join_Output” table as shown. In “Mappings” tab map the output columns accordingly.

SQL Yoga - Merge Join Transformation #8

12. Package Execution:

Execute the package and check for the results in the “Merge_Join_Output” table.SQL Yoga - Merge Join Transformation #9

13.Result in database

After successful execution of the package, we can check the result in “Merge_Join_Output” table.


  1: -- OLEDB Table 1
  2: SELECT * FROM Employee
  4: -- OLEDB Table 2
  5: SELECT * FROM Department
  7: -- Output data after Merge Join Operation
  8: SELECT * FROM Merge_Join_Output
  9: GO

SQL Result:

SQL Yoga - Merge Join Transformation #10

In this way we get the Merge Join result by combining both the tables data based on common data, such that it becomes easier to navigate information from the single merged table, instead of referring two different tables and link the related data.

Reference: Tejas Shah (

December 24, 2012

SQL SERVER: Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below:
As Mentioned in Image, Here, dates are dynamic, based on Order Date, 10/25,10/26,10/27,10/28 etc..To achieve the same with Dynamic columns please find query for the same:
CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
SELECT 1000029,100018,990.0000,'10/25/2012'
SELECT 1000029,100016,660.0000,'10/26/2012'
SELECT 1000029,100018,660.0000,'10/26/2012'
SELECT 1000029,206007,660.0000,'10/26/2012'
SELECT 1000029,206007,660.0000,'10/27/2012'

	,@MaxDate DATE

SELECT @Dates = ''

SELECT @MinDate = MIN(OrderDate)
	,@MaxDate = MAX(OrderDate)
FROM #TestTable

WHILE @MinDate <= @MaxDate
	SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10), @MinDate, 101) + '],'

	SELECT @MinDate = DATEADD(d, 1, @MinDate)

SELECT @Dates = LEFT(@Dates, LEN(@Dates) - 1)


SELECT @qry = N'

SELECT    id,
        ' + @Dates + '
    SELECT    id,
            ISNULL(Amount,0) AS Amount,
    FROM #TestTable t
) x
    SUM(Amount) FOR OrderDate IN(' + @Dates + ')
) p '

EXEC (@qry)

Let me know if you have any question/comment.
Reference : Tejas Shah

November 20, 2012

Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7. I need to configure reports in manner, so User can access this reports thru External IP Address. I found configuration for IIS 7.0, so user can access reports externally. To able to access the reports externally, do the steps as mentioned below:

1. Create Virtual Directory in IIS 7.0 :


2. Go to Handler Mappings :


3. Double Click on "Handler Mapping". You will get screen like the given below:


4. Now see Top Right Corner: Click On "Add Managed Handler". Here you need to fill up the info like:


Fill this info as:

Request path: Reserved.ReportViewerWebControl.axd
Type: Microsoft.Reporting.WebForms.HttpHandler
Name: Reserved-ReportViewerWebControl-axd

Thats it. Now try with External IP. You can access the reports with External IP too.

November 2, 2012

SQL SERVER: Limit memory usage of SQL SERVER

Recently, we have found that SQL SERVER is using most of the memory on the server, due to heavy queries are running on my different teams and it freezes the whole server. Due to time constraint team doesn’t have time to optimize those queries, so we have found a way to configure SQL SERVER to not to use all available memory. We have an option to configure how much memory can be used by SQL SERVER as below:

1. Connect to the SQL SERVER instance for which we need to limit memory usage and click on properties:

SQL SERVER Limit SQL SERVER Memory usage2. Go to “Memory”, where you can see the memory options that can be used by SQL SERVER as below:

SQL SERVER Limit SQL SERVER Memory usage SQL SERVER Properties
By default, SQL SERVER is using maximum server memory, so to resolve the freezing issue, we need to change it as per requirement. So SQL SERVER will use only that much memory and other processes can run in the server.

Let me know your comments.

Reference: Tejas Shah (

October 16, 2012

SQL SERVER: T-SQL to find out when database was restored

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database.

We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found:

FROM    msdb.dbo.RestoreHistory WITH (nolock)
WHERE    destination_database_name = '<Database Name>'
ORDER BY restore_date DESC

SQL Yoga T-SQL to find out when database was restored

Reference: Tejas Shah (

October 15, 2012

SQL SERVER: Clear Job History

Recently, we have implemented few jobs and we were testing the jobs. After our test runs successfully, we wanted to clear Job’s history, so we can have accurate job status. I have found following SQL to delete job’s history:

USE msdb

EXEC dbo.sp_purge_jobhistory
@job_name = '<Job Name>'

This will clear all history for the specified job. If you wanted to clear job history up to specific date, you can use following:
USE msdb

EXEC dbo.sp_purge_jobhistory
@job_name = '<Job Name>' ,

If you wanted to clear job history for ALL SQL jobs, you should just execute:

USE msdb

EXEC dbo.sp_purge_jobhistory

Reference: Tejas Shah (

October 11, 2012

SQL SERVER: Configure notification to send an email when JOB fails

In my previous post, T SQL to display Job history, I have mentioned to get list with the job statuses. One of my developer asked me to share, How to configure Notification to send an email when Job fails. Developers problem is, he is getting empty dropdown while configuring notification as shown in below figure:

SQL Yoga Configure notification to send an email when JOB fails #1
Here, This dropdown contains list of Operators, so we have to create operators to whom we can send a notification email. Its very simple to configure operator, see below:

SQL Yoga Configure notification to send an email when JOB fails, create Operator #2
Now, we have created Operator, so, lets set it up for the job as below:

SQL Yoga Configure notification to send an email when JOB fails #3
Now, Mail will be send to the email specified in “ErrorEmails” operator.

Note: Make sure to Configure Database email to send an email from the SQL SERVER.

Reference: Tejas Shah (

October 10, 2012

SQL SERVER: T-SQL to display Job history

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed.

We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query:

SELECT As JobName,
    run_date As RunDate,
    RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,
    CASE h.run_Status   
        WHEN  0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN  'Retry'
        WHEN 3 THEN 'Cancelled'
    ELSE ''
    END AS JobStatus,
    RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j on h.job_id = j.job_id
    AND h.step_id = 0
ORDER BY j.Name, h.run_date

Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows:

SQL Yoga T-SQL to display job history

We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments

Reference: Tejas Shah (

November 3, 2009

SQL SERVER: Configure Database Mail with SQL SERVER 2005

We have used Database mail to send mail to client on each updates.This is a very simple process to configure. Let me share how to configure Database mail with SQL server 2005 with all of you. After setting up Profile and Account properly, you just need to write following code to send a mail to client:

Step 1:

Configure Database Mail Step 1

Step 2:

Configure Database Mail Step 2

Step 3:

Configure Database Mail Step 3

Step 4: You might get this message:

Configure Database Mail Step 4

Step 5: Create Profile

Configure Database Mail Step 5

Step 6 : Create Account

Configure Database Mail Step 6

That's it.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html

Let me know if you have any complexity or comments in setting up Database mail.

December 24, 2008

SQL SERVER: How to Add Computed Column

Today I came across scenario where I need to add one computed column. Computed column means we can manipulate one or more column from the same table and we can use this column in queries as regular column.

Lets try it:

Create One Table: tblTestComputed

CREATE TABLE tblTestComputed(
FirstName VARCHAR(50),
LastName VARCHAR(50)

Lets insert some data into it:

INSERT INTO tblTestComputed(FirstName, LastName)
SELECT 'Tejas', 'Shah'
SELECT 'Hiral', 'Shah'

So now I run:

SELECT * FROM tblTestComputed

I will get output like:

Add Computed column

Now, I need display name as "Shah Tejas", "Shah Hiral" like that. so what I did is, I added one new column as:

ALTER TABLE tblTestComputed
ADD FullName AS (ISNULL(LastName,'') + ' ' + ISNULL(FirstName,''))

So, now if I run:

SELECT * FROM tblTestComputed

So, Output like:

Add computed Columns

So, I can use this new column "FullName" everywhere to display Full Name.

Computed Column is auto updated, means if FirstName OR lastName is changed then content of this column is also changed accordingly

One more thing We can not update computed column as it computed by other columns

Reference: Tejas Shah (