Joseph Michael Pesch
VP Programming

SQL Read Sharepoint Document Content for SSRS Reports

by 6. December 2012 09:43

I had a case where I needed to review a few hundred SSRS reports installed on a Sharepoint 2010 envrionment.  Below is a SQL script I created to read the content from the underlying SQL database and search the report structure for certain keywords (in my case I was looking for specific database/table names in the report SQL).

create table #tmp(name varchar(150), content varchar(max))
/* Get cursor of all SSRS report (*.rdl) files to cycle through */
declare cur cursor for 
select Id, LeafName, InternalVersion 
from AllDocs where LeafName like '%.rdl'
declare @id varchar(50), @name varchar(200), @version int
open cur
while 1=1 begin
  fetch next from cur into @id, @name, @version
  if @@FETCH_STATUS <> 0 break
  /* Check is current version of the report contains specific keywords */
  if exists
  (
    select 1 
	from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
	where Id = @id 
	and InternalVersion = @version 
	and 
	(
	  cast(Content as varchar(max)) like '%KEYWORD #1 HERE%' 
	  or 
	  cast(Content as varchar(max)) like '%KEYWORD #2 HERE%')
	)  
    insert into #tmp 
    select @name, cast(Content as varchar(max)) 
    from WSS_CONTENT_SITENAMEHERE.dbo.AllDocStreams 
    where Id = @id and InternalVersion = @version
end
close cur
deallocate cur
select * from #tmp
drop table #tmp

Tags:

Sharepoint | SQL Server

C# Get First and Last Day of Previous Month

by 3. December 2012 08:25

To get the first day of previous month:

var firstDayPrevMonth = new DateTime(DateTime.Today.Year
  , DateTime.Today.Month, 1).AddMonths(-1);

To get the last day of previous month:

var lastDayPrevMonth = new DateTime(DateTime.Today.Year
  , DateTime.Today.Month, 1).AddDays(-1);

 

Tags:

C#