SSRS – Download all RDL files from Report Server in one go.
Introduction
People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box. And I am sure that many of you might have come across this requirement some day or other. Hence, today I will share a simple… Read more
2 comments, 13,139 reads
Posted in Juggling With SQL on 7 August 2013
SQL Server – Generate Calendar using TSQL
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
9 comments, 13,167 reads
Posted in Juggling With SQL on 9 April 2013
SQL Server – Generating PERMUTATIONS using T-Sql
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
0 comments, 858 reads
Posted in Juggling With SQL on 11 March 2013
SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.
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
0 comments, 1,958 reads
Posted in Juggling With SQL on 7 March 2013
SQL Server # Database Backup Report using T-SQL
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
3 comments, 6,540 reads
Posted in Juggling With SQL on 18 October 2012
SQL Server # TSQL to Convert STRING in PROPER format
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
9 comments, 12,747 reads
Posted in Juggling With SQL on 15 October 2012
SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL
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
0 comments, 1,798 reads
Posted in Juggling With SQL on 28 March 2012
Last Execution Date Time of a Stored Procedure
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
1 comments, 2,341 reads
Posted in Juggling With SQL on 12 March 2012
How to hide Placeholder in SSRS 2008?
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
1 comments, 6,196 reads
Posted in Juggling With SQL on 11 February 2012
Fragmented Index – Rebuild or Reorganize ?
Introduction
In past, many times I have written on Fragmentation of Indexes and how to Defrag them. You can find some related articles at -
4 comments, 15,520 reads
Posted in Juggling With SQL on 12 January 2012
Dynamic Sorting in SSRS
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
8 comments, 19,930 reads
Posted in Juggling With SQL on 26 December 2011
How many times the T-SQL inside the CTE is executed?
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.
However,…
12 comments, 10,047 reads
Posted in Juggling With SQL on 15 December 2011
Workarounds for SSRS 2008 Font rendering issue
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
- Change the Screen Resolution…
5 comments, 5,838 reads
Posted in Juggling With SQL on 2 December 2011
Using OFFSET and FETCH
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 -
- High utilization of the network…
1 comments, 9,175 reads
Posted in Juggling With SQL on 30 November 2011
How to find out which Table is not having rows in SQL Server?
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
5 comments, 2,285 reads
Posted in Juggling With SQL on 1 October 2011
How to find a string value in all the string columns of a table/view in SQL Server ?
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
0 comments, 6,305 reads
Posted in Juggling With SQL on 30 September 2011
T-SQL to find Fragmented Indexes
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
2 comments, 7,201 reads
Posted in Juggling With SQL on 22 August 2011
Lookup functions in SSRS 2008 R2 - III
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…
1 comments, 11,149 reads
Posted in Juggling With SQL on 30 July 2011
Lookup functions in SSRS 2008 R2 - II
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
0 comments, 8,480 reads
Posted in Juggling With SQL on 27 July 2011
Lookup functions in SSRS 2008 R2 - I
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
7 comments, 34,164 reads
Posted in Juggling With SQL on 22 July 2011