ELMAH, NuGet, SQL Server Compact 4.0, and EF Code-First

ELMAH + NuGet + SQL Server Compact 4.0 + EF Code-First = Trouble!

Following Scott Hanselman's directions, I installed ELMAH on Microsoft SQL Server Compact 4.0 (SQL CE). Because of the dependency of 'ELMAH on MS SQL Server Compact' on SQL CE (and I already had the 'Manage NuGet Packages' window open) I let NuGet install it for me. NuGet installed everything perfectly and soon I was happily logging exceptions and storing them in a *.sdf file. Cool.

I also used NuGet to install Entity Framework 4.1 and followed Scott Guthrie's post on Code-First Development with Entity Framework 4. I created a model, added a connection string to my ASP.MVC 3 application, and soon I was persisting data to a second *.sdf file. Still cool.

Finally it was time to push my application to a server. This is where things became uncool.

Could not load file or assembly 'System.Data.SqlServerCe.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference.

After confirming that System.Data.SqlServerCe.Entity was in the bin directory, I started reviewing the versions of the SQL CE files, and then just to be sure I copied all of the files from my local installation of SQL CE to the server again. The problem still persisted.

I had used NuGet to install the version of SQL CE that ELMAH was using so I decided to research what the expected file versions were. These two great posts by ErikEJ caused me to formulate a theory that perhaps ELMAH and EF 4.1 were using the wrong or conflicting file versions. I made two changes to my application to test my theory.

1. Altered DbProviderFactories in my web.config file as described in this article.
2. Set up a binding redirect for System.Data.SqlServerCe as described in this article.

After making these two changes exception logging and data persistance to my two compact databases started working on the server. Maybe it's just me but it seems that a significant percentage of development troubleshooting is pure intuition!

TNSNAMES.ORA and spaces

Just when Oracle database and I had forged an uneasy peace, my day was derailed by my mistake.

When Oracle tells you that "[t]he service name must begin in the first space of a line to be parsed correctly" they are not kidding. I received a request from one of our DBA's to change a server's TNSNAMES.ORA file and copied and pasted the new entry. The three (3) leading spaces on that entry caused me to get "ORA-12154: TNS: could not resolve the connect identifier specified" errors for an hour until I realized my mistake.

Don't let this happen to you. Heed the warning of Oracle.

Linked Oracle Server configuration pain

For the past day I've been forcing myself to work with the Visual Studio Team System 2008 Database Edition GDR R2 for a small SQL Server 2000 database that needs some attention. I wanted Visual Studio to generate a SQL Server 2005 deployment script that I could execute against a local SQL Server 2008 Express instance in order to test a possible migration. During the initial execution of the script I was notified of an error involving a linked Oracle server. No problem, I thought. I'll just create the linked server and run the script again. As happens so often in software development, though, a seemingly quick and easy task turns into a quagmire.

It's been a while since I set up a linked server and I was a little foggy on the setup steps. I was able to determine by examining the existing SQL Server 2000 server that I needed to use the 'Microsoft OLE DB Provider for Oracle' provider. Still using the existing server, I was able to figure out how to fill in the 'Linked Server Properties' dialog and the Security options. As soon as I clicked the OK button, though, I was presented with an error:

The Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.

OK- maybe I needed to set the 'Provider string' value. Using this page as a reference (though I'm not configuring IIS), I was able to determine the correct Provider value ("Provider=MSDASQL;DRIVER={Microsoft ODBC for ORACLE};UID=User;PWD=Password;Server=Your_TNSNames_Alias") under item 9.

I recreated the linked server but it still didn't work. I looked in the Registry, and in fact created an ORACLE_HOME string value at HKLM\Software\Oracle. That didn't help either, so I removed that value. (I did notice that setting this value fixed something in Toad 9.7.2 but that story is out of scope for this post.) I even restarted my workstation a couple of times in an effort to appease the Oracle Client God. Nothing worked. My linked server was still broken.

Finally I examined my PATH environment variable. Now, I've struggled mightily with configuring Oracle clients because my company has versions of Oracle ranging from 8i-11. My PATH was collateral damage, and contained references to two Oracle installations. The first reference was to an 11 client's root, and the second was to a 10 client's bin directory. At some point in the past I think that I read that the probing / search for an Oracle client stops when the first client home installation is found. With this in mind, I altered the 11 client path element to point to the bin directory rather than the 11 client root. Restarted the SQL Server 2008 Express service and my linked server was alive and kicking.

So what have I learned after 4 hours of tinkering with SQL Server and Oracle client interaction? Well, there's got to be an easier way. It seems to me that the Oracle client setup and configuration process is opaque. Example: is ORACLE_HOME needed in the registry, as an environment variable, both places, or neither one? The answer seems to be that it depends on the application. I've always been lucky that my installations have worked on a server, but it seems like a fragile process for a volatile developer workstation.

Phantom TNSNAMES entries

I have a PowerShell script that uses Oracle.DataAccess. It retrieves its connection information from an existing configuration file on a server. The connection information is as basic as you can get: Data Source, User Id, Password. I had already successfully installed and executed the script on a development server but received the following error when I attempted to run the script on my test server: "ORA-12505: TNS:listener does not currently know of SID given in connect descriptor".

I was baffled. The test server is an application server that has been running in good order for a couple of years. The application that runs on it (let's call it SERVICE) successfully talks to the "missing" database almost continuously. The TNSNAMES file on the test server contains a single entry, and I knew that entry was valid because SERVICE was up and running.

I immediately started checking event logs, experimenting with case sensitivity in the connection string and the PowerShell script, and tweaking environment variables. I executed the relevant steps from the script in the PowerShell console. I scoured the server's file system for extra TNSNAMES.ORA files but found only the one that I expected. I stopped SERVICE in case it was somehow blocking my script's database calls. To make the issue even more confounding, I could specify a Data Source that wasn't even listed in the TNSNAMES file and I could then open the OracleConnection!

After several hours I gave up. I returned to the problem over a week later with a strange notion to check any mapped network drives. I've got a default mapped drive created (I assume) when my domain account was created. Looking in this drive I had a Eureka! moment: an old TNSNAMES file from my development machine that was full of entries. Suddenly it all made sense: I could connect to databases not present in the test server's TNSNAMES file because the entries were present in the 'network' TNSNAMES file. And, conversely, I couldn't connect to the test server's TNS entry because the port value had changed in the past couple of months and my 'network' TNSNAMES file had the old, invalid port.

What I had expected to be a trivial smoke test turned out to be much, much more. There's a lesson in here somewhere. The obvious one is that I probably should have initially started off configuring the script with the credentials with which it will be used in production. Another lesson is that software development can be maddeningly frustrating and that sometimes you just have to walk away. We don't all have the luxury of time that I did during this exercise, but sometimes some distance from a problem really brings clarity.

Windows 7 x64, Toad 9.7.2.5, Oracle 11g ODAC

After too much time and frustration, I finally got Toad (version 9.7.2.5) and Oracle Data Access Components to work on Windows 7 Professional x64.

For hours Toad wouldn't even acknowledge that an Oracle client was installed. I tried the 11g x86, Instant Client, 10g Oracle x64, finally Oracle 11g ODAC and Oracle Developer Tools for Visual Studio AKA ODTwithODAC1110621.zip started to work. At one point while trying to get Instant Client to work I manually set the ORACLE_HOME, TNS_ADMIN, and PATH environment variables. I don't know if that had any effect on my success. I'm too scared to remove them to find out considering the fragility of the Toad / Oracle client interaction. For now I'm just happy that my Dell Latitude E6500 can play any role other than netbook.

Corrupt TNSNAMES.ORA file

I just spent three hours troubleshooting this error: "ORA-12154: TNS:could not resolve the connect identifier specified." I had three TNSNAMES.ORA files with identical information. Oracle.DataAccess interaction with two of the three resulted in the ORA-12154 error. After scouring Google, I finally came across this nugget of wisdom:

Just a note, sometimes the TNSNAMES.ORA will get messed up. Nothing will jump out but it will not work and you cannot see the file though the net config. Just re-create it using notepad or [s]omething.

I replaced the two misbehaving files with the 'good' file and guess what? ORA-12154 magically went away.

NHibernate and Oracle 9i

Spent 1.5 hours troubleshooting the following error when using NHibernate 2.1:

"NHibernate.HibernateException: Could not instantiate dialect class NHibernate.Dialect.Oracle9Dialect ---> System.TypeLoadException: Could not load type NHibernate.Dialect.Oracle9Dialect. Possible cause: no assembly name specified."

I was looking at outdated documentation. The correct dialect is "NHibernate.Dialect.Oracle9iDialect" (emphasis mine). "NHibernate.Dialect.Oracle10gDialect" is also valid.