Report SSRS report definition from TSQL

Within this holiday season, it is really difficult to bring on a topic relates to techs. So I decided to make it simple and short: let’s talk about how to manage reports by T-SQL.

If you had read my previous posts, I had demonstrated how to read and modify report definitions by C# codes. But there could be situations that we want to read report definitions for specific purpose, e.g. report documentation.

To document the report definition, try below codes (assume there is a report named "test report"):  

 

USE ReportServer
go

;WITH XMLNAMESPACES   
 (   
 DEFAULT   
 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',   
 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'  
 AS rd   
 )  
 SELECT cte.name as ReportName  
   , ds.value('(@Name)[1]', 'VARCHAR(250)') as DatasetName  
      , ds.value('(Query/DataSourceName)[1]','VARCHAR(250)') as DataSourceName  
      , ds.value('(Query/CommandText)[1]','VARCHAR(250)') as Query  
      , fd.value('(@Name)[1]','VARCHAR(250)') as FieldName  
      , fd.value('(DataField)[1]','VARCHAR(250)') as DataField  
      , fd.value('(rd:TypeName)[1]','VARCHAR(250)') as DataType  
  FROM   
  (   
      SELECT C.Name  
           ,CAST(CAST(C.Content as VARBINARY(MAX)) as xml) AS reportXML  
      FROM ReportServer.dbo.Catalog C  
      WHERE C.Content is not null  
      AND C.Type = 2 and c.Path like '%test report%'  
 ) cte  
      CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(ds)  
      CROSS APPLY ds.nodes('Fields/Field') f(fd)   
 ORDER BY name   

It them will give you result like below screenshot:

The only thing needs to be noticed is about the XML namespace declaration. As shown above, the "2010/01" is for SSRS 2012. Depends on the edition of the SSRS you are using, you might need to change the declaration a bit. For instance, if the report was created from SSRS 2008, you need to change the definition to "2008/01".

Simple and easy, isn't it?

So that is it. The last day of 2015, and last post of 2015.  Happy New Year!

SQL - custom log schema via XML

It has been a long time since my last post: After took my holiday for about one month, it is just so difficult to get myself ready for work :(

But anyway, when I came back immediately I found there were quite a lot works need to be settled: We are changing our whole dialler to a brand new one; we are having some new opportunities from both internal and externals; we are planning to update our data platforms, such as ETL management frameworks, etc, etc.

When I reviewed our team's old works, I found below codes and just want to log here. I could not find the original source of the codes, but if my memory is correct, the credit of the codes should be to one of the SQL books. (professional SQL Server 20xx, probably) So if you know where the source of below codes is, please leave me a message so I can link to codes to original source.

The purpose of below codes is to log DML actions via XML and table triggers. Basically we will turn insert and update actions into xml format through the table trigger, and then log into the DML log table.

Now firstly we have table structure like below

 Create table SysLog.DMLLog  
 (  
 LogKey bigint identity(1,1),  
 TableName varchar(255),  
 TriggerName varchar(255),  
 SpID int,  
 Username varchar(255),  
 DMLAction XML,  
 CreatedOn datetime,  
 Constraint PK_DMLLog primary key(LogKey)  
 )  
 go  

Fields we created are self-explanatory, so I won't spend time to explain it :p.

Now it is time about the trigger, assume we are going to build the trigger on Account table. Something like below:

 Create Trigger TriLogAccountDML   
 On Account  
 After insert, update, delete  
 as  
 BEGIN  
      DECLARE @XmlAction XML, @TriID int, @TblID int  

      SELECT @XmlAction = ISNULL((SELECT * FROM inserted FOR XML AUTO), '<inserted/>') + ISNULL((SELECT * FROM deleted FOR XML AUTO), '<deleted/>') 
 
      SELECT @TriID = @@PROCID  
      SELECT @TblID = parent_id FROM sys.triggers WHERE object_id = @TriID  

      INSERT INTO SysLog.DMLLog(TableName, TriggerName, SpID, Username, DMLAction, CreatedOn)  
      SELECT OBJECT_NAME(@TblID), OBJECT_NAME(@TriID), @@SPID, SYSTEM_USER, @XmlAction, GETDATE();  
 END  

Now we are done. In the end we will have our log table like below:

Quite simple isn't it? :D