Checking IIS 7 Event Logs

One of the simplest ways to troubleshoot IIS issues is to check the event logs. This can be done by running the Event Viewer. Click the Windows Start button, type "eventvwr" and press enter. Or if this is too hard to remember, simply type event and the Event Viewer should show under Programs in the search results.

The Event Viewer can easily be found by typing 'event' in the search box after clicking the Windows Start button

Go to Windows Logs -> System and on the right panel look for events from WAS.

Event Viewer System Log

You can also create a custom view to only find the WAS events. Right click on Custom Views -> Create Custom View -> Click the "By source" radio, select WAS as "Event Source" and click OK.

A first chance exception of type 'System.NullReferenceException'

While debugging an ASP.NET application, I kept encountering errors similar to the following:

A first chance exception of type 'System.NullReferenceException' occurred in MyApp.dll
A first chance exception of type 'System.ArgumentException' occurred in Microsoft.VisualBasic.dll

At first I was simply ignoring the error since my application appears to be working, and except for the assembly DLL name, it doesn't tell me which source file or line number the error is occurring in. Then I happen to find this nice little feature to help me. Go to the Debug menu -> Exceptions -> then drill down to Common Language Runtime Exceptions -> System and look for the specific exception that you're having problems with.




Check the Thrown checkbox, uncheck the User-unhandled checkbox and rerun. Visual Studio will now break at the point where the exception occurs.

Adding httpModule Handlers in web.config Not Working in IIS 7

There are several tutorials on how to use HTTP modules to extend ASP.NET processing. If you google httpModules, the first several entries point to the Microsoft websites that teach you how to create and configure them. I used this one in particular.

Extending ASP.NET Processing with HTTP Modules

If you follow all the instructions and copy and paste their code, it may not work for you if you are using IIS 7. The problem is that the tutorial was not updated for later versions of IIS (as of the posting of this blog article) and IIS 7 changed the configuration for custom HTTP modules within web.config. The tutorial tells you to add the following in your web.config:

<configuration>
  <system.web>
    <httpModules>
      <add name="RequestTimeIntervalModule" type="Samples.Aspnet.HttpModuleExamples.RequestTimeIntervalModule">
      </add>
    </httpModules>
  </system.web>
</configuration>

Which is how it is in previous versions of IIS. But IIS 7 looks for it here.

<configuration>

  <system.webServer>

    <modules>

      <add name="RequestTimeIntervalModule" type="Samples.Aspnet.HttpModuleExamples.RequestTimeIntervalModule">

      </add>

    </modules>

  </system.webServer>

</configuration>

Note that it is possible to put both sections in your web.config in case you want to run your web application in both versions of IIS. Also, the project migration tool within VS 2008 does not automatically add the corresponding entries in system.webServer even if you have  httpModules defined in your web.config, so this is something you have to add manually when you're upgrading IIS to version 7.

Upgrading the Data Access Components for Analysis Services from Windows XP to Windows 7

I had a project where I needed to upgrade a .NET web application.

Before the upgrade, it had the following environment:

• Development: Visual Studio 2003, Windows XP
• Production: SQL Server 2000, Analysis Services 2000, Windows Server 2003
• Data Access: Microsoft Data Access Components 2.8 (MDAC 2.8) 

After the upgrade, it had the following environment:

• Development: Visual Studio 2008, Windows 7
• Production: SQL Server 2008, Analysis Services 2008, Windows Server 2008
• Data Access: Windows Data Access Components 6.0 (WDAC 6.0)

The first thing that you need to know is that MDAC 2.8 has been renamed to WDAC 6.0. While MDAC needed to be installed separately, WDAC is a core component of Windows Vista and later operating systems, which means there is no separate distributable for installing WDAC. In trying to minimize any potential code changes, I initially tried to reuse MDAC 2.8 by copying Interop.ADODB.dll and Interop.ADOMD.dll to the new environment and added them as references in my projects, but I got the following error:

The project currently contains references to more than one version of Interop.ADODB, a direct reference to version 2.8.0.0 and an indirect reference (through 'ADOMD.Member.Properties') to version 6.0.0.0. Change the direct reference to use version 6.0.0.0 (or higher) of Interop.ADODB.

I couldn't find anything on the web on how to fix this error, so after several hours of trying to get this to work, I decided that it would take less effort to just use WDAC instead. But it took me a few more hours of research to finally figure out how to add references to the new WDAC DLL's.

1. Select the Build -> Clean Solution menu
2. Delete all instances of Interop.ADODB.dll and Interop.ADOMD.dll in all the project directories (usually in the obj or obj/Debug folders).
3. For each project that uses the old MDAC 2.8 components, replace references to Interop.ADODB.dll to msado15.dll by right clicking on the project -> Add Reference, click on the COM tab, and look for Microsoft ActiveX Data Objects 6.0 Library as shown by the first highlighted line below.
4. For each project that uses the old MDAC 2.8 components, replace references to Interop.ADOMD.dll to msadomd.dll by right clicking on the project -> Add Reference, click on the COM tab, and look for Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library as shown by second highlighted line below.
5. Do a full rebuild of the projects and all data access related code should magically compile without any code changes.

Configuring SSIS 2008 to Connect to Oracle

Connecting SSIS to an Oracle database is a lot more difficult than it really should be. There are a couple of reasons for this. The first is because BIDS only runs in 32 bit mode even if you have a 64 bit operating system, but SQLAgent will run as a 64 bit process in a 64 bit operating system. The second is that there is more than one way of getting it to work, so various sites will give seemingly conflicting information, which may be applicable for one case, but not another.

The following are the high level steps I used to get SSIS 2008 to connect to an Oracle database.

1. Install the correct version of the Microsoft Connector for Oracle by Attunity.

Go to the Microsoft Connector for Oracle v1.2 download page if you're using SSIS 2008.
Go to the Microsoft Connector for Oracle v2.0 download page if you're using SSIS 2012

Download the 32 bit version if you're using a 32 bit OS, and the 64 bit version if you're using a 64 bit OS. Note that the 64 bit version includes both the 32 and the 64 bit binaries.

2. Install the Oracle Instant Client. If you're using 64 bit Windows, you'll need to download and install BOTH 32 and 64 bit versions of the client software.

Get the 32 bit Oracle Instant Client here.
Get the 64 bit Oracle Instant Client here.

There were some folks on other sites who couldn't get the Oracle Instant Client to work with SSIS, and therefore advocated installing the full version of the Oracle database software instead. The downside with this of course is that you'll end up eating a lot more disk space and memory than what is necessary, not to mention the wasted software licenses. The above pages also contain links for downloading Sql*Plus, which I like to use for testing purposes.

The Oracle Instant Client packages do not come with their own install programs. You'll have to manually unzip them to directories of your choosing. If you're installing both versions, you should unzip them to different directories. I like adding x32 and x64 to the directory names so I can easily differentiate between them. Note that as of version 11.2, all these zip files unzip to the same subdirectories by default, so I would unzip them in the following order.

• Unzip Oracle Instant Client 32 bit
• Unzip Sql*Plus 32 bit
• Rename subdirectory by inserting _x32 to the directory name. In my case, the path is x:\oracle\instantclient_x32_11_2.
• Unzip Oracle Instant Client 64 bit
• Unzip Sql*Plus 64 bit
• Rename subdirectory by inserting _x64 to the directory name. In my case, the path is x:\oracle\instantclient_x64_11_2.

You also need to create your own tnsnames.ora file. Set the TNS_ADMIN environment variable to point to the directory where you the put tnsnames.ora, and add the Instant Client directory to the Path variable. I use the 64 bit version in the Path in my case, but the 32 bit version should work, too. It should be noted that if you're not familiar with tnsnames.ora, manually creating that file yourself is not trivial. It would be best if you can work with an Oracle DBA to create the file for you.

3. Configure the Microsoft Connector and the Oracle Instant Client.

Follow the instructions here for installing and configuring the Oracle Connector. It shows the registry entries needed to allow the Microsoft Connector to recognize the Instant Client, and how to add the Oracle Source / Destination to the BIDS Data Flow toolbox. If you have carefully followed the instructions here and in the linked pages, you should now be able to add your Oracle data flows into your package.