Introduction

Recently, I was asked to develop a SSRS based report for the Event Management module in MS Dynamics CRM 2011. The idea was to show a Calendar for the selected month and each cell of the calendar should display the scheduled events of that day.

Showing the events in… Read more

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to

Read more

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve… Read more

Today, I am going to share few very useful scripts which will report us on Database Backup from different view points.

To get the List/History/Log of all the Successful Backups

SELECT 

  b.machine_name,

  b.server_name,

  b.database_name as DBName,

  b.backup_start_date,

  b.backup_finish_date,

  CASE 

    WHEN b.[type] = 'D' THEN 'Database'

    WHEN b.[type] = 'I' THEN

Read more

Problem Statement

SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does not provides any direct way to convert it to PROPER format. A string/text is said to be in a PROPER format if all the words in that string/text starts… Read more

Introduction

Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is -

Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the… Read more

On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.

Actually speaking, without explicit logging or tracing, it is not possible to get this… Read more

Introduction

For the ones who are working with SSRS, Placeholder is not new for them. So, today I will not go into explaining what is Placeholder, How and Where it is used, etc. I will straight away target the issue mentioned and try to explain a possible way to overcome… Read more

Introduction

In past, many times I have written on Fragmentation of Indexes and how to Defrag them. You can find some related articles at -

• T-SQL to find Fragmented Indexes
• What is the best value for Fill Factor in SQL Server?
• Rebuild all the indexes of a table & Indexed…

Read more

Introduction

Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -

Implementation

Assuming that the… Read more

Read more

Problem

A report developed using SSRS 2008 either through Visual Studio 2010 or BIDS 2008 does not renders properly when viewed in the Report Viewer 10.0 via Remote Desktop or any 3rd party applications using Terminal Services in the background.

Remote Rendering

Local Rendering

Workarounds

1. Change the Screen Resolution…

Read more

Introduction

Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -

1. High utilization of the network…

Read more

Introduction

Many times while tuning our production databases we might try to find out the list of tables not having even a single row of data. Today, I am going to show a simple script which could be used to get a list of tables having ZERO rows.

Script

USE

Read more

Introduction

I am sure many times we all might have come across situations where we need to search/find a string value in all the string columns of a given table/view in SQL Server and return the matching rows from that table.

Unfortunately, we do not have any straight forward… Read more

Fragmentation of Indexes is one of the reason for low performing queries resulting in a poor application performance.

Today, I will present a simple script which will help in identifying the level of fragmentation in a Database.

--Replace this with the name of the Database for which we want to

Read more

Introduction

This is the final post to discuss the last Lookup function Multilookup(). The other two – Lookup() and LookupSet() have already been discussed in my previous posts.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

source_expression – The field which will act as the key/lookup value for the destination. This will be…

Read more

 Introduction

In my last post on Lookup functions related to SSRS 2008 R2, I had explained Lookup(). It is used to fetch the first matching value from the other DataSet. Now, what if we want all the matching values from the other DataSet. Here, the LookupSet() function comes handy.… Read more

Introduction

Most of us who are regularly working with SSRS have always felt the need of some way through which multiple DataSets could be joined – something similar to JOINs of SQL. But there was no straight forward way to get it done in SSRS until the release of… Read more

Introduction

In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.

Implementation

USE DBName

GO

Read more