About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Wednesday 7 August 2013

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 handy script which would help you to download all the required report files at once.

Implementation

SSRS uses SQL Server to store it’s details as a backend and the Catalog table is used to store the report file in binary form. The below script simply pulls the report definition from the Catalog table & uses BCP utility to export the same at a pre-defined path as a .rdl file.

To use the BCP utility from TSQL, we need to execute  “xp_cmdshell” command; it is disabled by default. So, first you need to execute the below script to enable it -

-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
 
-- Update the currently configured value for advanced options.
RECONFIGURE
GO
 
-- Enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
 
-- Update the currently configured value for xp_cmdshell
RECONFIGURE
GO
 
-- Disallow further advanced options to be changed.
EXEC sp_configure 'show advanced options', 0
GO
 
-- Update the currently configured value for advanced options.
RECONFIGURE
GO

Once successfully executed, the below script with the required changes could be executed to download the files -



--Replace NULL with keywords of the ReportManager's Report Path, 
--if reports from any specific path are to be downloaded
DECLARE @FilterReportPath AS VARCHAR(500) = NULL 
 
--Replace NULL with the keyword matching the Report File Name,
--if any specific reports are to be downloaded
DECLARE @FilterReportName AS VARCHAR(500) = NULL
 
--Replace this path with the Server Location where you want the
--reports to be downloaded..
DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\'
 
--Used to prepare the dynamic query
DECLARE @TSQL AS NVARCHAR(MAX)
 
--Reset the OutputPath separator.
SET @OutputPath = REPLACE(@OutputPath,'\','/')
 
--Simple validation of OutputPath; this can be changed as per ones need.
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = ''
BEGIN
  SELECT 'Invalid Output Path'
END
ELSE
BEGIN
   --Prepare the query for download.
   /*
   Please note the following points -
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc.
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer]
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that.
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding. 
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one. 
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF). 
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”. 
      While it is supported, it can cause problems with the conversion to XML, so it is removed.
   */
   SET @TSQL = STUFF((SELECT
                      ';EXEC master..xp_cmdshell ''bcp " ' +
                      ' SELECT ' +
                      ' CONVERT(VARCHAR(MAX), ' +
                      '       CASE ' +
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                      '         ELSE C.Content '+
                      '       END) ' +
                      ' FROM ' +
                      ' [ReportServer].[dbo].[Catalog] CL ' +
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                      ' WHERE ' +
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                    FROM
                      [ReportServer].[dbo].[Catalog] CL
                    WHERE
                      CL.[Type] = 2 --Report
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                    FOR XML PATH('')), 1,1,'')
  
  --SELECT @TSQL
  
  --Execute the Dynamic Query
  EXEC SP_EXECUTESQL @TSQL
END

Conclusion


Hope, this helps & save a lot of your valuable time.


Happy Reporting!

Monday 8 April 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 the required format in each cell was not a big issue. The main challenge was to generate a dynamic grid of Calendar. Luckily, the CRM was deployed on-premises and I got a chance to use TSQL to generate the Calendar.

Implementation

Below is the TSQL which I came up with to generate the Calendar -

DECLARE @Month AS INT = 4 --Set the MONTH for which you want to generate the Calendar.
DECLARE @Year AS INT = 2013 --Set the YEAR for which you want to generate the Calendar.
 
--Find and set the Start & End Date of the said Month-Year
DECLARE @StartDate AS DATETIME = CONVERT(VARCHAR,@Year) + RIGHT('0' + CONVERT(VARCHAR,@Month),2) + '01'
DECLARE @EndDate AS DATETIME = DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate))
 
;WITH Dates AS (
  SELECT 
    @StartDate Dt
  UNION ALL
  SELECT 
    DATEADD(DAY,1,Dt) 
  FROM 
    Dates 
  WHERE 
    DATEADD(DAY,1,Dt) <= @EndDate
),Details AS (
  SELECT 
    DAY(Dt) CDay,
    DATEPART(WK,Dt) CWeek,
    MONTH(Dt) CMonth,
    YEAR(Dt) CYear,
    DATENAME(WEEKDAY,Dt) DOW,
    Dt 
  FROM 
    Dates
)
--Selecting the Final Calendar
SELECT
  Sunday,
  Monday,
  Tuesday,
  Wednesday,
  Thursday,
  Friday,
  Saturday
FROM
  (SELECT CWeek,DOW,CDay FROM Details) D
PIVOT
(
  MIN(CDay)
  FOR DOW IN (Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
) AS PVT
ORDER BY
  CWeek

Output:


Calendar


Hope, this will help!

Monday 11 March 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 be permuted
DECLARE @NoOfChars AS INT = LEN(@Value)
DECLARE @Permutations TABLE(Value VARCHAR(20)) --Make sure the size of this Value is equal to your input  string length (@Value)
 
;WITH NumTally AS (--Prepare the Tally Table to separate each character of the Value.
  SELECT 1 Num
  UNION ALL
  SELECT 
    Num + 1 
  FROM 
    NumTally 
  WHERE 
    Num < @NoOfChars
),Chars AS ( --Separate the Characters
SELECT
  Num,
  SUBSTRING(@Value,Num,1) Chr
FROM
  NumTally  
)
 
--Persist the Separated characters.
INSERT INTO @Permutations
SELECT Chr FROM Chars
 
--Prepare Permutations
DECLARE @i AS INT = 1
WHILE(@i < @NoOfChars)
BEGIN
 
  --Store the Permutations
  INSERT INTO @Permutations
  SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated  Chars.
    P1.Value + P2.Value
  FROM 
    (SELECT Value FROM @Permutations WHERE LEN(Value) = @i) P1 
  CROSS JOIN 
    (SELECT Value FROM @Permutations WHERE LEN(Value) = 1) P2
  
  --Increment the Counter.      
  SET @i += 1  
  
  --Delete the Incorrect Lengthed Permutations to keep the table size under control.
  DELETE FROM @Permutations WHERE LEN(Value) NOT IN (1,@i)
END
 
--Delete InCorrect Permutations.
SET @i = 1
WHILE(@i <= @NoOfChars)
BEGIN
 
  --Deleting Permutations which has not used "All the Chars of the given Value".
  DELETE 
  FROM 
    @Permutations
  WHERE
    Value NOT LIKE '%' + SUBSTRING(@Value,@i,1) +'%'
  
  --Deleting Permutations which have repeated incorrect character.  
  DELETE 
  FROM 
    @Permutations
  WHERE
    LEN(Value) - LEN(REPLACE(Value,SUBSTRING(@Value,@i,1),'')) != 
    LEN(@Value) - LEN(REPLACE(@Value,SUBSTRING(@Value,@i,1),''))
    
  SET @i += 1  
END
 
--Selecting the generated Permutations. 
SELECT Value FROM @Permutations

Hope, this script helps!


Please share your suggestions if you have any to improve this logic.