All posts by Jonathan Briggs

Business Intelligence, Excel, OfficeWriter

Creating a Sales Pipeline Report with ExcelWriter

One of the great features of ExcelWriter is Grouping and Nesting feature of ExcelTemplate. Grouping and Nesting allows you to easily format your data in order to make it more readable. This feature is especially handy when applied to a common report, such as a sales pipeline report. Internally at SoftArtisans, we use ExcelWriter with Grouping and Nesting when generating our own sales pipeline report! By taking advantage of this feature, you can get fancier looking reports with little additional effort – ExcelWriter does the formatting work for you!

Before we begin, let me say that I will not attempt to tell you how to query your data as I’m sure everyone will be using different CRM systems. However, no matter how you’re querying your data (be it SQL, or a web service like SOAP or REST) the process is generally the same.

To start, you need to decide how you want to group your data. In most cases you’ll want to group first by month or quarter; then you could group by salesperson. These can be whatever you want, but you must have a column in your result set that will represent each.

For example, we group our own sales report data by: Continue reading Creating a Sales Pipeline Report with ExcelWriter →

Drupal

Drupal on Windows: Using SQL Server Merge Replication

In my first “Drupal on Windows” blog post, I wrote about using Windows authentication with SQL Server. That’s a recommended best practice for SQL Server in any production environment. So with the knowledge that Drupal can run on Windows technologies in a production environment, how can we scale Drupal to achieve high availability? How do we move beyond a single web farm?

One solution is:

  • Geographically dispersed web farms using Global Load Balancing
    • Each web farm resides in it’s own data center
    • Each web farm utilizes it’s own SQL Server instance
  • SQL Server Merge Replication
    • Keep the database in each web farm in sync, so the served content is identical

SQL Server Merge Replication

Setting up merge replication (and global load balancing) is beyond the scope of this blog post, but there are lots of great instructions out there. I would recommend the following reading for those not familiar with merge replication: Continue reading Drupal on Windows: Using SQL Server Merge Replication →

Drupal

Drupal on Windows: Using Windows Authentication with SQL Server

With the release of Drupal 7, it became easy to run the whole Drupal stack on Windows technologies. Instead of requiring a LAMP stack (Linux, Apache, MySQL, PHP), you now have the option of a WISP stack (Windows, IIS, SQL Server, PHP). The excellent Drupal 7 driver for SQL server makes using a SQL Server backend with Drupal possible. Even better, the underlying PDO (PHP Data Objects) driver for SQL Server supports Windows authentication! But wait… there are no instructions provided on using Windows authentication to SQL Server with Drupal! I knew that in theory it should be possible. After some tweaking, I finally figured it out. So how do you set it up? Here are the missing instructions…

Getting Windows Authentication working with SQL Server

Before you start, you will need either:

  • A domain user account, with both web server and SQL server joined to the domain
    -or-
  • Identical user accounts on both servers (same username and password); in this case the servers do not need to be joined to a domain

In my example below, I will use the identical user accounts method. Omit step 1 and substitute your domain account information wherever step 1 is referenced if you want to use the domain account method. Continue reading Drupal on Windows: Using Windows Authentication with SQL Server →

SharePoint, SQL Server, Windows Server

Windows Server 2008 R2 domain controller: Fixing SceCli Event 1202

When running IIS, SQL Server, or SharePoint on a Windows Server 2008 R2 domain controller, you may encounter this error:

Application Event
Event ID: 1202
Source: SceCli
Security Policies were propagated with warning. 0x534 : No mapping between account names and security IDs was done.

I ran into this error when building a virtual machine to run a self contained SharePoint environment, which required that the VM also be a domain controller. This event will show up repeatedly every few minutes in the application event log. The error occurs because the domain controller doesn’t have a concept of “local” accounts and doesn’t know how to resolve some account names that are added by IIS and SQL Server to the domain controller security policy. Microsoft has released a hotfix 977695 to resolve the issue. Continue reading Windows Server 2008 R2 domain controller: Fixing SceCli Event 1202 →

SharePoint

Using PowerShell to Convert SharePoint 2007 List Templates (STP) for SharePoint 2010

2 Comments
This method of converting SharePoint 2007 list templates for use in SharePoint 2010 is not supported by Microsoft. Use at your own risk. Your mileage may vary.

SharePoint 2007 List Templates in SharePoint 2010

I recently needed to move a number of list templates from SharePoint 2007 to SharePoint 2010. Unfortunately, SharePoint 2010 just doesn’t support this. The only supported method to move these lists across is to upgrade the whole content database. That just wasn’t an option for me, so I needed to find another way.

Luckily, I discovered an excellent blog post by Tom (Belgium) that describes a method for getting SharePoint 2007 list templates working in SharePoint 2010.

Tom’s basic method is:

  1. Extract the contents of the STP file (it’s really just a CAB file)
  2. Edit the manifest.xml file, changing the ProductVersion element from 3 to 4
  3. Repackage the STP file

I have had success with this method for both templates for lists and document libraries. Continue reading Using PowerShell to Convert SharePoint 2007 List Templates (STP) for SharePoint 2010 →

ASP.NET

Upgrading IIS Web site or application to ASP.NET 4.0

I want to upgrade a Web site from ASP.NET 2.0 to 4.0 without resetting IIS because it is running on a production server. The tool to do this is the ASP.NET IIS registration tool, aspnet_regiis.exe, which comes with ASP.NET 2.0 and 4.0. This tool doesn’t come with ASP.NET 3.0 and 3.5 because they’re both based on CLR 2.0. In fact, you don’t set a Web site or application to use ASP.NET 3.0 or 3.5, as IIS only knows about CLR version. See Scott’s Hanselman’s article for an excellent explanation. The .NET Framework version 4.0 comes with the new CLR 4.0 and so a new version of the tool is available.

Since I want to upgrade the Web site to ASP.NET 4.0, I’d run the 4.0 version of aspnet_regiis.exe, which is found under %systemroot%\Microsoft.NET\Framework\v4.0.303319. According to MSDN, to avoid interrupting IIS, I should use the -s switch to specify only the desired site or application as well as the -norestart switch to inhibit a restart. The value for the -s switch should be the path to the site; however, it may not be clear what this value should be.

To get a list of the paths and their registered ASP.NET version, I executed aspnet_regiis -lk and got the the following: Continue reading Upgrading IIS Web site or application to ASP.NET 4.0 →

Windows Server

Configuring SSL Bindings Directly for Http.sys

I ran into an issue today with how the IIS 7.0 admin GUI deals with SSL certificates when assigning bindings to web sites. I had two websites that I was binding to the same IP address, but I was using different ports for each (including different ports for SSL). Even though I was using a different SSL port for the second website, it was telling me that my certificate was already in use by another website and that changing the setting would affect the other site. The strange thing was, I was using two completely different certificates. Why in the world would it tell me my certificate was in use on the other website, when it clearly was not? Changing the SSL settings on one site would end up deleting the settings on the other site. After searching online, I found out that there are some known bugs with how the admin GUI deals with bindings and SSL in general. By settings the bindings on the command line, I was able to work around the issue.

Below are some useful command line commands that can assist in creating SSL bindings manually.

To list SSL certificates in use, with their bindings: Continue reading Configuring SSL Bindings Directly for Http.sys →

ASP.NET, Windows Server

Running ASP.NET 1.1 on Windows Server 2008 R2

Although this configuration works on Windows Server 2008 R2, it is unsupported by Microsoft. Use at your own risk.

Use these steps to install ASP.NET 1.1 on either Windows Server 2008 x64 SP2, or Windows Server 2008 R2.

Short version:

  1. Follow all of the steps in How to install ASP.NET 1.1 with IIS7 on Vista and Windows 2008
  2. Then implement this workaround for an acknowledged bug: Workaround: Running ASP.NET 1.1 on Vista SP2/WS08 SP2

My summary:

  1. Ensure that the “IIS Metabase Compatibility” Role Feature is installed in IIS
  2. Download and install:
  3. Make sure ASP.NET 1.1 is enabled under ISAPI and CGI Restrictions
    • In my experience, this has already been enabled after installation
  4. Add this IgnoreSection handler to the <configSections> element on the .NET 1.1 machine.config, located in %windir%\Microsoft.NET\Framework\v1.1.4322\CONFIG