I have a web application that was using SubSonic version 2.1.0. While adding some new features I came across a bug in that version that was fixed in 2.2.0.
On my dev box, I switch the version of the DLL referenced and everything worked fine.
After updating the Windows 2008 R2 server running IIS 7.5, the bug has persisted.
I searched the server and replaced every instance of SubSonic.dll with the latest version.
Restarted the site, the app pool and then the entire server.
I ran SysInternal's Process Explorer and checked the w3wp.exe process for the site and according to it, w3wp.exe is referencing version 2.2.0 of SubSonic.dll.
I've copied the database and site files from that Windows 2008 server to another 2008 (one that never had v2.1.0 loaded) and confirmed the bug doesn't occur there.
Based on the symptoms, it seems that the server is holding on to the 2.1.0 version of the DLL but I cannot figure out where it has it and how to get rid of it.
Additional Info:
I checked the GAC, no SubSonic dlls.
Installed 2.2.0 into the GAC using gacutil from the Windows SDK 6.1
Setup a new Site and AppPool on the server and uploaded a fresh copy of the site files from my dev box.
Site is C# .Net 2.0 using MVC 1 with nHaml 2.0 for the view engine.
Used cygwin find to search the filesystem for any files with the same size as the 2.1 DLL.
Found a couple copies that the Windows search box didn't, they were just in a backup folder, shouldn't have been in use, deleted just in case.
The only remaining files with the same size as the 2.1.0 version are:
./Windows/System32/DriverStore/FileRepository/prnca00z.inf_amd64_neutral_27f402ce616c3ebc/Amd64/CNBDR4_5.DLL
./Windows/winsxs/amd64_microsoft-windows-getuname.resources_31bf3856ad364e35_6.1.7600.16385_en-us_eca42f29f7e4d0ea/getuname.dll.mui
./Windows/winsxs/amd64_prnca00z.inf_31bf3856ad364e35_6.1.7600.16385_none_ea189c313845a10e/Amd64/CNBDR4_5.DLL
./Windows/winsxs/x86_microsoft-windows-getuname.resources_31bf3856ad364e35_6.1.7600.16385_en-us_908593a63f875fb4/getuname.dll.mui
Which appear to have nothing to do with the SubSonic DLLs.
Update 8-30-2010
Reasoning regarding bug for suspecting the DLL version in use is the issue:
The error that is happening is rather specific and on my development box when I change between 2.1.0 and 2.2.0 of the DLL it predictably happens under 2.1.0 and not 2.2.0.
I've also put the site to a different server and it operates fine with 2.2.0, so it working is not unique to my development box.
Basically, in version 2.1.0 when doing a paged result query the WHERE clause elements are doubled up so the generated query would end up with "WHERE CreatedOn > '8-1-2010' AND CreatedOn > '8-1-2010'".
Although redundant, syntactically this is fine to execute.
When you add in a SubQuery is when it goes wrong because the SubQuery object has its SQL generated twice and the second time instead of starting with a WHERE it starts with an AND because a boolean flag on the object tracking if the WHERE has been started is true at the start from the first time generating the SQL.
So under 2.1.0 you will get "WHERE id IN (SELECT id FROM table WHERE CreatedOn > '8-1-2010') AND id IN (SELECT id FROM table AND CreatedOn > '8-1-2010')"
In 2.2.0, the WHERE clause on a paged query doesn't repeat its conditions and so the SubQuery doesn't generate improper SQL syntax.
The SQL generation occurs within the DLL and I can observer through SQL Profiler that 2.1.0 generates the bad syntax but when I run locally with 2.2.0 the syntax is proper.
Because this error is such a specific situation, the site works fine in general, it is on just a specific search query that this happens and it is very easily to repeat that with no code, data or data structure or other environment changes, it errors under 2.1.0 but not 2.2.0.
I hadn't given specifics on the bug previously as it doesn't seem relevant to solving the issue, the resolution should be regarding any 3rd party dll loaded from the bin directory of an asp.net site being cached and not updating version after app pool, service and machine reboots, new site container creation, deletion and reupload of all site files, etc.
Update: Try deleting the content of:
Do this for the relevant path ( depending on your version of .net and 32/64 bit).
Before update: I am not familiar with subsonic. But maybe it generates assembly or stored procedures. And somehow the old version of this things is being kept even after the dll upgrade. Use process monitor to check what the application is loading from disk. And check also the stored procedures if it is relevant.
A couple of questions:
While I must admit that I didn't read your problem in it's entirety, I'd like to guess at what the problem might be. I tried to post this as a comment, but the site wouldn't allow me, for some reason.
The problem might not be in the Subsonic DLL itself, but in another DLL that it references. Version A of Subsonic.dll might not reference this other DLL at all, but version B might reference it, and hence this is why you are getting different behaviour, and can't locate the problem.
You need to use some good debugging tools, to step through the flow of events one at a time, until you find something that doesn't look right.
I also think that this is question more suited to StackOverflow. I believe this is a programming-related problem, especially with regards to the assembly references.
Try posting on StackOverflow as well, you will get people looking at this problem from a different angle then your usual Server Admins.
Try the following:
Change App Pool identity account to admin.
Check if the Windows Server 2008 R2 is not a debug version.
Check out this link - IIS7 caches stuff for quite a long time, so you might want to rebuild the cache.
http://blogs.iis.net/ksingla/archive/2006/11/16/caching-in-iis7.aspx
I've found the issue.
As I was doing more testing on various machines, I was able to reproduce the error finally.
Site Code + SubSonic 2.1.0 = error
Site Code + SubSonic 2.2.0 + SQL Server 2008 = works
Site Code + SubSonic 2.2.0 + SQL Server 2008 SP1 = error
It happens that in 2.1.0 the error happens no matter what, with 2.2.0 the issue was fixed. There is an additional logic error that if you are using 2008 SP1 a new error occurs but its symptoms match the original issue I had in 2.1.0.
After noticing the SQL database version difference and confirming by upgrading my development box and then the error occurred, I've been able to find some additional information now that I knew I was looking at a new issue.
https://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme
http://github.com/subsonic/SubSonic-2.0/issues
Issue 7 specifically, but 2, 8 and 9 all appear to be closely related and will probably present with the same malformed SQL syntax.