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!

Read More

How to collect cluster Information using TSQL

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script. And then the script will provide me the details I needed.
This inventory should have details like below for cluster and stand alone instances:
Server Name            
OS Name               
OS Edition   
OS Patch Level   
SQL Server IP       
Is Clustered   
Node1_Name        
Node1_IP           
Node2_Name           
Node2_IP           
SQL Server Edition   
SQL Server Patch Level       
Server Time Zone       
SQL Server Version   
SQL Server Platform   
Processor Core       
Physical Memory       
Service Account Name        
Domain               
Looks good ? Below is the version 1 of this script.

/*
IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000

Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com
Date  : 24th March 2013
Version  : 1.0

Tested ON:
Windows Server  >> 2003, 2008, 2008 R2 
SQL Server  >> 2000, 2005, 2008, 2008 R2, 2012 

*/
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @TimeZone   NVARCHAR(100)
  ,@ProductVersion SYSNAME
  ,@PlatForm  SYSNAME
  ,@Windows_Version SYSNAME
  ,@Processors  SYSNAME
  ,@PhysicalMemory SYSNAME
  ,@ServiceAccount SYSNAME
  ,@IPAddress  SYSNAME
  ,@DOMAIN  SYSNAME
  ,@MachineType  SYSNAME
  ,@SQLServerIP  VARCHAR(255)
  ,@CMD   VARCHAR(100)
  ,@Node1   VARCHAR(100)
  ,@Node2   VARCHAR(100)
  ,@Node1IP  VARCHAR(100)
  ,@Node2IP  VARCHAR(100)
  ,@OSEdition  VARCHAR(100)
  ,@OSVersion  VARCHAR(100)
  ,@OSName  VARCHAR(100)
  ,@OSPatchLevel  VARCHAR(100)
   
CREATE TABLE #TempTable
 (
  [Index] VARCHAR(2000),
  [Name] VARCHAR(2000),
  [Internal_Value] VARCHAR(2000),
  [Character_Value] VARCHAR(2000)
 ) ;

INSERT INTO #TempTable
EXEC xp_msver;

-- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
       @value_name   = N'StandardName', 
       @value        = @TimeZone output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
       @value_name   = N'ObjectName', 
       @value        = @ServiceAccount output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductType', 
       @value        = @MachineType output

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',
       @value_name   = N'Domain', 
       @value        = @DOMAIN output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\1',
       @value_name   = N'NodeName', 
       @value        = @Node1 output
       
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'CLUSTER\NODES\2',
       @value_name   = N'NodeName', 
       @value        = @Node2 output              

EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'ProductName', 
       @value        = @OSName output              
       
create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100)) 
insert into #OSEdition 
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
       @value_name   = N'ProductSuite'  
SET @OSEdition = (SELECT TOP 1 OSedition  FROM #OsEdition)       
               
EXEC   master.dbo.xp_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE',
       @key          = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
       @value_name   = N'CSDVersion', 
       @value        = @OSPatchLevel output  

set @cmd = 'ping ' + @Node1 
create table #Node1IP (grabfield varchar(255)) 
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd 


set @cmd = 'ping ' + @Node2
create table #Node2IP (grabfield varchar(255)) 
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd 
 
set @cmd = 'ping ' + @@servername
create table #SQLServerIP (grabfield varchar(255)) 
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd 

SET  @SQLServerIP=( 
      SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
      from #SQLServerIP  where left(grabfield,7) = 'Pinging' 
    )
SET  @Node1IP    =(
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node1IP  where left(grabfield,7) = 'Pinging' 
     )
       
SET  @Node2IP  =( 
       SELECT substring(grabfield,  charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) 
       from #Node2IP  where left(grabfield,7) = 'Pinging' 
    )
       
SET  @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2)
SET  @Platform  = (SELECT Character_Value from #TempTable where [INDEX]=4)
SET  @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15)
SET  @Processors  = (SELECT Character_Value from #TempTable where [INDEX]=16)
SET  @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19)


SELECT 
  ServerName   = @@SERVERNAME  
 ,OSName    = @OSName
 ,OSEdition   = @OSEdition
 ,OSPatchLevel          = @OSPatchLevel
 ,SQLServerIP          = @SQLServerIP
 ,IsClustered          = SERVERPROPERTY('IsClustered')
 ,Node1_Name   = @Node1
 ,Node1_IP   = @Node1IP 
 ,Node2_Name   = @Node2
 ,Node2_IP   = @Node2IP 
 ,SQLServerEdition         = SERVERPROPERTY('Edition')
 ,SQLServerLevel          = SERVERPROPERTY('ProductLevel')  
 ,ServerTimeZone          = @TimeZone 
 ,SQLServerVersion         = @ProductVersion
 ,SQLServerPlatform         = @PlatForm
 ,ProcessorCore          = @Processors
 ,PhysicalMemory          = @PhysicalMemory 
 ,ServiceAccountName         = @ServiceAccount 
 ,WKS_Server   = @MachineType
 ,Domain    = @DOMAIN
 
GO 
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition 
GO


sp_configure 'xp_cmdshell',0
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO

Please do post back your feed back for this script, I will try my best to update and post back new version for this script.
- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

photo credit: Skimaniac via photopin cc

Read More

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:





Hope, this will help!

Read More

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.

Read More

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 those records.
Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process.
I quickly opened up the SSMS and came up with the below script -

USE < DBName > --Replace this with the actual DBName
GO

DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria
DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query

--The below will list the TSQL Statements which could be copied & executed in a separate query window.
SELECT 'IF EXISTS(SELECT 1 FROM ' + T.NAME + ' WHERE ' + @Criteria + ') ' + 'SELECT ''' + T.NAME + ''' TableName, * FROM ' + T.NAME + ' WHERE ' + @Criteria
FROM sys.columns C
INNER JOIN sys.tables T
 ON T.object_id = C.object_id
WHERE C.NAME = @ColumnName

The above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.


Hope, this helps!

Read More

SQL Server # Moving MASTER database in cluster environment

Few months back I have wrote post about moving MASTER and MSDB database to new location in stand alone machine.

In recent past we had a situation where customer asked us to move MASTER database to new location, below are the steps I have taken:

1.     Connect to the Server
2.     Open Configuration Manager -> SQL Server Service
3.     Right Click and say Properties
4.     Click on the Start-up Parameter
5.     Remove start-up parameter (the highlighted one)

	 -dOLDLocation\master.mdf
	 -eOLDLocation\ErrorLog
	 -lOLDLocation\mastlog.ldf

 

      6.     Add new start-up parameters with new values (per your configuration)

   

	 -dNewLocation\master.mdf
	 -eNewLocation\ErrorLog
	 -lNewLocation\mastlog.ldf

      7.    Check and confirm which node is active

      8.    PAUSE current PASSIVE Node  to avoid fail-over


      9.    Take SQL Server resources offline, i.e. SQL Server, SQL Agent, MSDTC, SQLCLUSTER Name (do not take SQL Cluster IP Offline)


    10.    Copy MASTER.MDF and MASTLOG.LDF to NEW Location ( S:\SQLDATA, yours could be different)


    11.    Log into Cluster Administrator and bring SQL Server Resources online


    12.    Resume current PASSIVE Node

 

That's all, you should be able to see your master database on new location now!!!

 

-- Regards,

Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Cross posting: http://www.pythian.com/news/35829/moving-master-database-to-new-location-in-sql-cluster/

Read More

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 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,    

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

ORDER BY 

  b.backup_start_date DESC

GO

To get a list of all successful Backups taken till date for a particular Database

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'
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 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

WHERE

  b.database_name = @DBName  

ORDER BY 

  b.backup_start_date DESC

GO

To get the List of all Databases which are not backed up till date

SELECT

  d.name [DB_Name]

FROM

  master.sys.databases d

LEFT JOIN msdb.dbo.backupset b

  ON b.database_name = d.name

WHERE

  d.database_id IS NULL

To get the List of all Databases which are not backed up since last X days

DECLARE @LastXDays AS INT = 1

;WITH LatestBackupSet AS (

SELECT 

  b.database_name as DBName,

  b.backup_start_date LastBackedUpOn,

  b.[user_name],

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

)

SELECT 

  lbs.DBName,

  lbs.LastBackedUpOn,

  lbs.[user_name]

FROM 

  LatestBackupSet AS lbs

WHERE

  DATEDIFF(DAY,lbs.LastBackedUpOn ,CURRENT_TIMESTAMP) = @LastXDays  

  AND lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get a list of the Latest successful backups of all Databases

;WITH LatestBackupSet AS (

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 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location,

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

)

SELECT 

  machine_name,

  server_name,

  DBName,

  backup_start_date,

  backup_finish_date,

  Backup_Type,

  expiration_date,

  [user_name],

  Total_Time_in_Minute,

  recovery_model,

  Total_Size_GB,

  Location

FROM 

  LatestBackupSet AS lbs

WHERE

  lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get the Latest successful backup of a particular Database

DECLARE @DBName AS VARCHAR(100) = 'Your Database Name'

 

;WITH LatestBackupSet AS (

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 'Differential database'

    WHEN b.[type] = 'L' THEN 'Log'

    WHEN b.[type] = 'F' THEN 'File or filegroup'

    WHEN b.[type] = 'G' THEN 'Differential file'

    WHEN b.[type] = 'P' THEN 'Partial'

    WHEN b.[type] = 'Q' THEN 'Differential partial'

    ELSE b.[type]

  END Backup_Type,

  b.expiration_date,

  b.[user_name],

  DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,

  b.recovery_model,

  b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,

  bf.physical_device_name as Location,

  ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk

FROM 

  msdb.dbo.backupset AS b

INNER JOIN msdb.dbo.backupmediafamily AS bf

  ON b.media_set_id=bf.media_set_id

WHERE

  b.database_name = @DBName  

)

SELECT 

  machine_name,

  server_name,

  DBName,

  backup_start_date,

  backup_finish_date,

  Backup_Type,

  expiration_date,

  [user_name],

  Total_Time_in_Minute,

  recovery_model,

  Total_Size_GB,

  Location

FROM 

  LatestBackupSet AS lbs

WHERE

  lbs.Rnk = 1

ORDER BY 

  lbs.DBName DESC

GO

To get a list of Databases that were backed-up and do not currently exist

SELECT

  DISTINCT b.database_name

FROM

  msdb.dbo.backupset b

WHERE

  DB_ID(b.database_name) IS NULL

Hope, the above given script will be of help to you. Also, I would like to request you to please add any relevant script which you feel would be useful as a comment.

Read More

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 with a CAPITAL letter.

E.g. If a string is - “Hello, how are you?”,

String in Lower format = “hello, how are you?”

String in Upper format = “HELLO, HOW ARE YOU?”

and String in Proper format = “Hello, How Are You?”

 

Implementation

Ideally, SQL Server is not the right place to implement this kind of logic, as string operations are costlier in SQL from performance perspective. it should be either implemented in the Front-End language or the Reporting Tool as this more related to the formatting. However, if this is to be implemented in SQL, the more preferred way is to use SQL-CLR function. It does not mean that we can not achieve this with T-SQL.

Today, I will share a simple T-SQL function, which could be used to convert any given string in PROPER format. Below is the script -

CREATE FUNCTION [dbo].[PROPER]

(

  @StrToConvertToProper AS VARCHAR(MAX)

) 

RETURNS VARCHAR(MAX) 

AS

BEGIN

  --Trim the Text

  SET @StrToConvertToProper = LTRIM(RTRIM(@StrToConvertToProper))

  --Find the No. of Words in the Text

  DECLARE @WordCount AS INT

  SET @WordCount = LEN(@StrToConvertToProper) - LEN(REPLACE(@StrToConvertToProper,' ','')) + 1

  --Variable to track the space position

  DECLARE @LastSpacePosition AS INT = 0

  --Loop through all the words

  WHILE(@WordCount > 0)

    BEGIN

      --Set the Space Position

      SET @LastSpacePosition = CHARINDEX(' ',@StrToConvertToProper,@LastSpacePosition + 1)

      --Replace the Character

      SET @StrToConvertToProper = STUFF(@StrToConvertToProper,

                                        @LastSpacePosition + 1,

                                        1,

                                        UPPER(SUBSTRING(@StrToConvertToProper, @LastSpacePosition + 1, 1)))

      --Decrement the Loop counter                                      

      SET @WordCount = @WordCount - 1

    END

  RETURN @StrToConvertToProper

END  

When the above script is used as –

SELECT dbo.PROPER('hello, how are you?')

we get the following result - Hello, How Are You?

Conclusion

The given script could be used to convert any string in PROPER format using T-SQL. However, I would personally prefer converting the string at the Front-End or in the Reporting tool to display the string in this format.

Read More

Troubleshooting Oracle Link Server - Part2

It's my bad that I did not made a post since long lately. I would not make an excuse like I was busy, rather, I would say I am little lazy DBA, but I now onward I will try to post blog more frequently.

Well, if you remember, earlier I have posted some tips about troubleshooting Link Server issue for Oracle, today I will be posting one more tip to the list aka extending Troubleshooting Oracle Link Server. 

Few weeks back one of the developer reached out to me needing an assistance with Oracle Link Server. The issue with him was, whenever he execute T-SQL against Oracle Link Server, SQL Server was throwing an error at him which reads like OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX" returned message "Cannot start more transactions on this session." I'd connected to the server using SSMS and have checked permissions, Link Server and Provider configuration - nothing was changed. Then what could have cause this error? well, I will need to RDP to the box then to find an answer.     

But before that, as usual, upon questionnaire session, I came to know that developers doesn't have direct access to box and they all were using Citrix to connect to the SQL Server box hence they would not have any idea on what's going on with the physical box. As a DBA I had a privileges to RDP to the development box (obviously ), as soon as I logged in I'd notice that the box is running out of space. The drive which was running out of space was hosting:

• User Profiles
• TempDB
• & UserDB which they were using for ETL purpose for Oracle

And, as expected, once I'd reclaim some space everything was back to normal. 

Regards

Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Read More

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 table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee.

Problem Statement

This table structure very well serves the purpose as long as we have 1-Level hierarchy. However, if the hierarchy is of n'th level, the SELECT statement to fetch the records becomes more complex with this kind of table structure. Suppose, we want to fetch the complete TREE of a particular employee, i.e. list of all the employees who are directly or indirectly managed by a particular employee. How to do it……..?

Thanks to CTE’s for making the life a bit easier – as using them in a recursive manner, we can get the work done. Please follow this msdn link to see an implementation using recursive CTE.

Suggested Table Structure

Here, I have just included a new column [PATH]. It is of VARCHAR(MAX) type. I have taken it as VARCHAR(MAX) just to make sure the field is long enough to store the complete path. But one can assign appropriate size as per their system’s requirement.

The basic idea of the [path] column is to store the complete hierarchical path of any employee separated by a delimiter as under -

Calculating the new path is very simple. It’s just, {New Path} = {Parent Path} + {Self ID} + {Delimiter}

Now, suppose if I want to fetch all the employees who are directly or indirectly working under EmployeeID = 2, I can use the below tsql -

;WITH CTE AS (

SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]

UNION ALL    

SELECT 2 EmployeeID,1 ManagerID, '\1\2\' [Path]

UNION ALL    

SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]

UNION ALL    

SELECT 4 EmployeeID,2 ManagerID, '\1\2\4\' [Path]

UNION ALL    

SELECT 5 EmployeeID,4 ManagerID, '\1\2\4\5\' [Path]

)

SELECT

  *

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

We can use a simple logic to even find out the level of the Employee -

SELECT

  *,

  (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]

FROM

  CTE

WHERE

  [Path] LIKE '%\2\%'

2 is subtracted from the formula as the length of delimiter for Level-0 is 2.

Conclusion

Hope, this simple trick could save a lot of time for the ones who find themselves lost playing with the hierarchical data.

Read More

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 information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures.Please note that this view has been introduced from SQL Server 2008.
The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.
So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -

USE DBName
GO
SELECT 
 O.name,
 PS.last_execution_time
FROM 
 sys.dm_exec_procedure_stats PS 
INNER JOIN sys.objects O 
 ON O.[object_id] = PS.[object_id] 
GO

P.S. Please replace the DBName with the actual name of the Database.


The above script will return the name of all the cached stored procedure of the current database with their last execution time.


For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)

Read More

Moving MASTER Database

In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step.

Step 1: Query sys view and note down the existing location for MASTER database

USE MASTER 
GO 
SELECT 
NAME, 
PHYSICAL_NAME AS 'PhysicalFilePath', 
STATE_DESC AS 'DB Status' 
FROM SYS.MASTER_FILES 
WHERE NAME LIKE 'Mast%'

Step 2: Run alter database command and change the location for database files

ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MASTER, 
FILENAME= 'C:\SQLDB\Demo\Master.mdf' 
) 
GO 
ALTER DATABASE MASTER 
MODIFY FILE 
( 
NAME = MastLog, 
FILENAME= 'C:\SQLDB\Demo\MastLog.mdf' 
) 
GO 

Step 3: Stop SQL Server Service and move database files to new location

Step 4: Restart SQL Server Service, surprised ?

 

Step 5: This was expected, let’s see what errorlog has to say about this!

Refer the highlighted section, SQL Server service could not find the files. This is because we have moved that files to new location.

Step 6: Okay, so let’s go and change the start-up parameter. We can do this using Configuration manager.

Step 7: Right click on SQL Server service –> Properties –> Start-up Parameter

Step 8: Make correction in path for Master.mdf and Master.ldf

Step 9: Start SQL Server service, this time it will start.

You are done!!

Note: This is to be done when we have to do relocate databases to new drive, or file organization, or some error which force us to do this.

-- Hemantgiri S. Goswami

Read More

Moving MSDB to new location

In recent past we have a situation where in we required to move MSDB, Model and Master databases to new location, the reason being faulty drive. While moving system databases to new location we need to be extra cautious. Let’s see the process step-by-step.

Step 1: Let’s query sys view and note down the location for database files

SELECT
NAME,
PHYSICAL_NAME AS 'PhysicalFilePath',
STATE_DESC AS 'DB Status'
FROM SYS.MASTER_FILES

Step 2: Run alter database and specify new location for database

SELECT
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBData,
FILENAME= 'C:\SQLDB\Demo\MSDBData.mdf'
)
GO
ALTER DATABASE MSDB
MODIFY FILE
(
NAME = MSDBLog,
FILENAME= 'C:\SQLDB\Demo\MSDBLog.ldf'
)
GO

Step 3: Stop SQL Server service

Step 4: Once SQL Server service is stopped move MSDB database to new location
Step 5: Now, start SQL Server service. This time it will use the new path that we have configured in Step 2.
Note: If you have enabled and configure Database Mail, please make sure it works after you moved MSDB to new location.

Tomorrow, I will post about how to relocate Master database.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/)

Read More

Register for SQL Server 2012 Virtual Launch

`As we all are aware SQL Server 2012 virtual launch is scheduled after 60 hours roughly. Earlier I have discussed what’s new in SQL Server 2012 setup, MS has released SQL Server 2012 training kit and made it available for us to download. In recent past, I have blogged about a free eBook for SQL Server 2012 written by Ross Mistry ( Blog | Twitter ) and Stacia Misner ( Blog | Twitter) .

You will see lots of new and insightful information on new and enhanced features introduced in SQL Server 2012, this includes:

• AlwaysOn
• Automating AlwaysOn management
• New backup and restore capabilities
• ColumnStore index
• Semantic Search
• User-defined server roles
• Data quality service
• StreamInsight for SQL Server 2012
• Big data analytics and Hadoop
• T-SQL Enhancements
• SQL Server Data Tools (SSDT)
• SQL Azure

Sounds interesting ?

Get yourself register for the event, you will find the agenda of the event here.

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/ )

Read More