At Info et Cetera, we have a really nice content management system that we developed a number of years ago, which we have in place at several clients.  While there are lots of content management systems commercially available today that probably have much more advanced features than our system, we like our CMS because it lets us build lots of custom database functionality to integrate into our clients’ sites.  Additionally, our system provides complete separation between the back end management system and the front end templating system in a very convenient and flexible manner.  But enough about that…

This week, I deployed our CMS for a client on a Windows Server 2008 hosting platform.  All went pretty well putting the application on IIS7 / Windows Server 2008, with the exception of having to ask the host to turn on detailed error messages for Classic ASP so we could debug small errors.  One snag we hit was that we had to change our connection string.  Our old connection string looked like this:

Provider = SQLNCLI;Data Source = servername;Initial Catalog = databasename;User ID = username;Password = password;

However, when we tried that in IIS7, we got an error:

ADODB.Connection error ‘800a0e7a’
Provider cannot be found. It may not be properly installed

So I did some searching, and found a suggestion to change the connection string.  This made sense, I thought:

Driver={SQL Server};Server=servername;Database=databasename;Uid=username;Pwd=password;

All seemed to be fine; the application connected to the database.  But then we started running into a few situations where strings retrieved from queries were coming back empty.  Even Len(rst(“field”)) was returning “0”.  I suspected that the common thread was that these database fields were all defined as varchar(max).  However, other queries were able to pull results from varchar(max) fields without any problem.  It seemed like the troublesome queries all had grouping / aggregation functions.

After some digging, I found a post on the ISS.net forums that pointed me in the right direction.  It appeared that the problem was with the provider I was using.  Apparently to take advantage of the many of the new data types in SQL Server 2005 from ADO, you have to use the SQL Server Native Client OLE DB provider instead of SQLOLEDB.  So what the article recommended was using “Provider = SQLNCLI”.  But wait — that’s the way it was when I first brought the application over, and it said the provider could not be found.  More information can be found in an MSDN article titled Using ADO with SQL Server Native Client.  So on an off-chance, I tried the following connection string:

Provider = SQLOLEDB;Data Source = servername;Initial Catalog = databasename;User ID = username;Password = password;

Success!  That ended up doing the trick.  But that’s kind of strange.  It’s in direct conflict with what the MSDN article stated.  But it worked.  So the only thing I can figure is that the SQL Server OLE DB provider in Windows Server 2008 has been updated from what was in Windows Server 2003 and now includes all of the new features from SQL Server 2005.

So it ended up being a pretty simple solution of just changing the provider in the connection string, but a long path to get there.  Hopefully anyone else running into this problem will find this post and save a bit of time!

Update (8/24/2010):
Simon Sawyer contacted me to let me know that there’s a new provider for SQL Server 2008 that might do the trick to solve this problem. Simply replace “SQLNCLI” with “SQLNCLI10”.