Podcast interview with the creator of CSFBL (me!)

Earlier this week I had the pleasure of being a part of my first podcast! ZubaZ, a long-time player and active community member of CSFBL (the multiplayer baseball game I’ve been running for over 15 years), interviewed me for the 25th episode of his podcast series, Threads of Time. We talked about the history of the game, revealed some of its secrets, and outlined some of the plans for the future.

It was a lot of fun — and inspiring. For a long time, I knew that the game was good and that the community was great — and this podcast just further reinforced those thoughts for me. I have to give a serious hat-tip to ZubaZ, who made it easy to talk for nearly two hours about something that I’ve spent countless hours of my life working on.

Thanks to ZubaZ, and the entire CSFBL community for giving the game the longevity it has.

Check out the podcast: Threads of Time Ep25 – Brian

Which SSRS reports are being run through Dynamics CRM?

Microsoft Dynamics CRM uses SQL Server Reporting Services (SSRS) for its reporting platform. It’s a fairly decent integration, but it lacks one visible aspect from CRM: what reports are being run?

Getting detailed report execution (who requested the report, what parameters were used at runtime) would require digging deep, but if you just want to know which reports were run and when they were run, you can do it from two SQL queries.

For a list of all reports run for a CRM instance, use the SQL below. Replace “CRM_MSCRM” with the name of your CRM database, and CRM_ReportServer with the name of your SSRS reporting database.

with ReportNames as (
	select r.name, '{' + cast(r.reportid as nvarchar(40)) + '}' as reportid
	from CRM_MSCRM..FilteredReport r
FROM CRM_ReportServer.dbo.Catalog C with(nolock)
left outer join ReportNames rn on c.Name = rn.ReportID
LEFT OUTER JOIN CRM_ReportServer.dbo.ExecutionLog  E with(nolock) ON C.ItemID = E.ReportID
WHERE C.type=2
and rn.Name is not null
order by e.TimeStart desc

If you want a count of all reports run, along with the first/last date they were run, use the following (again replacing CRM_MSCRM and CRM_ReportServer as necessary): Continue reading

Forcing IIS to rewrite all requests to HTTPS

I often need to ensure that an IIS web site uses HTTPS instead of HTTP. The easiest way to do this is with a URL rewriting rule. The rule I use is below.

<rule name="Redirect to HTTPS" stopProcessing="true">
    <match url="(.*)" />
    <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
        <add input="{HTTPS}" pattern="^OFF$" />
    <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="Permanent" />

This will redirect any request that is not “HTTPS” to an “HTTPS” address.

Query a SQL server to find the progress of a database restore

This just came in from a co-worker. Too valuable not to share!

In case you might want to monitor the progress of a database restore on your SQL server, this query shows the progress in percentage, elapsed time, etc…

	r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
	CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
	CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
	CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
	CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
	CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
		CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
		FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

With that, you can really tell how far along a SQL restore is!