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
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
b.database_name as DBName,
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN
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
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
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
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…
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 -
Assuming that the… Read more
Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.
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.
- Change the Screen Resolution…
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 -
- High utilization of the network…
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.
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
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…
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
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
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.