April 4, 2015

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:

June 14, 2014

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:

MessageDatabase 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: xxx.xxx.xxx.xxx]

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):

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 (www.SQLYoga.com)

April 17, 2014

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.

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.

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.

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.

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.

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

“Merge Join Left Input”

“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.

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.

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:

Inner Join

Left Outer Join

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.

10. Result table creation:

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

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.

November 20, 2012

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:

November 2, 2012

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:

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

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.

October 16, 2012

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:

October 15, 2012

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 msdbGO

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:

October 11, 2012

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:

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:

Now, we have created Operator, so, lets set it up for the job as below:

Now, Mail will be send to the email specified in “ErrorEmails” operator.

October 10, 2012

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:

November 3, 2009

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:

December 24, 2008

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.