What’s my Programmer Competency (take 2)

I stumbled across an old blog post of mine, What’s your Programmer Competency?, where I outlined my self-analysis results using Sijin Joseph‘s Programmer Competency Matrix. I figured it was worth revisiting this and see how much things have changed in the eight years since that original blog post.

The Programmer Competency Matrix is measured on a scale from 0 to 3, with 3 being highest.

  • Computer Science: 1.3. Back in 2008, I was a 1. The reasons for the low number today is the same as eight years ago: my computer skills were not learned in the classroom, but in the real world. I’m not a computer scientist (as per the academic definition of computer science). But that’s not a bad thing, because most of the business world doesn’t need computer scientists!
  • Software Engineering: 2.7. Now we’re cooking. The only reason this isn’t a 3 is because I don’t have much experience in automated functional testing and UI testing. This is one area where I’ve continually developed my skills over the years, and the past 8 years have been no exception to that.
  • Programming: 2.9. This is a huge category so I won’t give myself a 3.0, but I’ve got most things in this category nailed. Reusable code? Check. Published frameworks? Check. Ridiculously well-organized code? Absolutely!
  • Experience: 2.3. I’ve got a lot of experience in the technologies that I work with, and I have light experience in a lot of other technologies. Not having experience in things like Erlang and Prolog drags down my score here, but I’m not selling myself as an Erlang or Prolog expert, so I’m happy with my score.
  • Knowledge: 2.4. If my career track was 100% development (and not more of a development/management split) this score would have been higher, but I’ve long ago accepted that part of my value is not just my programming knowledge, but my ability to interact with and communicate with people, not just machines — which is why I’m not spending my entire days sitting behind a computer writing code.

Not bad for a guy who started off as an infrastructure engineer, became a programmer out of necessity and curiosity, and ultimately made a career out of it all. Who would have thought this would happen when I was 12 years old hacking an Atari 800?

Microsoft’s PowerBI Knowledge Base offers lorem ipsum (and little more)

From a co-worker:

This is a screenshot of Microsoft’s new PowerBI dashboard platform. I needed to talk to their tech support so I clicked the “contact support” link and got this screen. Again, this is not a preview, or a beta, but rather their live support request form for a production product.

Look closely, and those informative knowledge base articles have topics like “Article 1” and “Long test article”, not to mention the ubiquitous, “Lorem ipsum dolor sit amet…” I wonder if they used the Lorem Ipsum generator for the text?

Microsoft PowerBI Knowledge Base fail

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
)
SELECT
	rn.Name,
	E.TimeStart,
	E.TimeEnd,
	E.Status
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$" />
    </conditions>
    <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="Permanent" />
</rule>

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…

SELECT
	r.session_id,
	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!

Stopping Internet Explorer 11 from showing the mobile express version of Dynamics CRM 2011

If you have a Dynamics CRM 2011 installation, and users have started using Internet Explorer 11 (either by upgrading from IE10, or by installing Windows 8), then you’ve undoubtedly noticed that, when an IE11 user goes to your CRM site, they are greeted by the Mobile Express version, rather than the standard site. There are a few manual workarounds to this:

  • Downgrade from IE11 to IE10 (not an option for Windows 8).
  • Add your CRM domain name to the compatibility list in IE11 (not an option if you don’t want the entire domain to be in compatibility mode, or if you have group policy settings which prohibit this).
  • Instruct users to go to https://yourcrmdomain.com/main.aspx.

That third bullet is interesting… If you go to the root of your CRM domain name in IE11, you will be redirected to the Mobile Express site. If you go to the “main.aspx” page on the root, you go to the full CRM site. Which got me thinking… How can we identify IE11 users accessing the Mobile Express site, and redirect them to /main.aspx?

The solution is in IIS’s URL Rewrite library, which, if you don’t already have, you should get, because I’ve used it before to fix issues related to Dynamics CRM (not to mention its multitude of other uses).

Here’s what our rewrite rule will do:

The full IIS rewrite rule is below. You can drop this into the web.config in the root of your Dynamics CRM 2011 web site, or set it up manually using the URL Rewrite wizard. Either way, when you’re done, you’ll always get the full version of Dynamics CRM when using the IE11 browser.

<rule name="Redirect IE11 from mobile site to main.aspx" patternSyntax="Wildcard" stopProcessing="true">
 <match url="*m/default.aspx" />
 <conditions logicalGrouping="MatchAll" trackAllCaptures="false">
 <add input="{HTTP_USER_AGENT}" pattern="*; rv:11.0) like Gecko" />
 </conditions>
 <action type="Redirect" url="https://crm.innovatix.com/main.aspx" redirectType="Temporary" />
</rule>

By the way, there is only one gotcha: You can’t use IE11 and deliberately go to the Mobile Express site. A small price to pay to fix a much larger problem.

Clearing the Web Platform Installer’s installer cache

My laptop has two hard drives — a 128GB SSD and a 1TB old school hard drive (taking the place of the DVD drive, which I never used). I love the SSD, but it’s size limits it to the operating system, some important program files, and a handful of documents.

Every now and then, it fills up. I recently found a silent disk space usage offender: Microsoft’s Web Platform Installer. Apparently, the Web Platform Installer (WebPI) caches all installer files to a local folder, and never deletes them. Which isn’t a problem if you have plenty of disk space, but for me, it’s a problem.

The path to the WebPI installer cache folder is “%LOCALAPPDATA%\Microsoft\Web Platform Installer\installers” – where the variable LOCALAPPDATA points to a folder in your user profile directory. For example, on my machine, the full path is C:\Users\demarzo\AppData\Local\Microsoft\Web Platform Installer\installers.

Deleting all the files in this folder is safe, as the WebPI will simply re-download what it needs when it needs it, which should be perfectly fine for most people who don’t make a habit of repeatedly reinstalling the same version of software over and over again. For me, it saved over 1GB of disk space. Which tells me, among other things, I install a lot of software!

Getting Dynamics CRM ObjectTypeCode values via SQL

Lots of tables in Dynamics CRM use the ObjectTypeCode to identify the entity. Sure, we all remember that Account is ObjecTypeCode 1 and Contact is ObjectTypeCode 2… But what about your custom entities, which start at 10,000 and are not guaranteed to be the same in different organizations, even if you have the same solutions installed — how will we remember their ObjecTypeCode values?

You don’t remember them, you query them when they need you. It’s quick and easy if you are on premises and have access to the SQL database. (If you don’t, ask your DBA to create a view with this query and give you rights to it.)

select coalesce(OriginalLocalizedName,name) as DisplayName, Name as SchemaName, ObjectTypeCode
from EntityLogicalView
order by ObjectTypeCode

Much easier than memorizing, and avoids the problems of remembering the wrong number for the wrong organization or deployment. Oops!