search

subsonic / SubSonic-2.0

SubSonic 2.0 Home
http://subsonic.github.io/
Other
80 stars 45 forks source link

DataService uses ANSISqlGenerator for Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 #7

Closed blankers closed 12 years ago

blankers commented 15 years ago

Problem: In Utitlity.cs, SubSonic.Utilities.IsSql2008() tests for provider.DatabaseVersion using the following logic at line 199:

return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 || 
    provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1 ;

You will obviously fail this test if your provider.DatabaseVersion == "Microsoft SQL Server 2008 (SP1) - 10.0.2531.0". And then you'll end up using the AnsiSqlGenerator.

Resolution: 1) return provider.DatabaseVersion.IndexOf("2008 ") > -1 ; or 2) return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 || provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1 || provider.DatabaseVersion.IndexOf("2008 (SP1) - 10.") > -1 ;

Reference: http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme

ManiacZX commented 14 years ago

Found that this is an issue for me too after upgrading from 2.1.0 to 2.2.0 to fix an issue with using IN SubQuery with Paged but production server seemed to still be having the issue in 2.2.0

http://serverfault.com/questions/174166/asp-net-site-on-iis-7-5-not-taking-new-version-of-3rd-party-dll

I don't have R2 to see what the DatabaseVersion response is, but I'm assuming it will be an issue for that too. I think suggested resolution 1 or similar needs to be used or this will continue to re-occur when updates are released by MS.

ManiacZX commented 14 years ago

Pulled latest code as of 9-1-2010 from GitHub and used suggested replacement in IsSql2008() by Mark M at http://stackoverflow.com/questions/1711798/subsonic-2-2-sqlquery-object-generates-very-different-sql-for-where-in-stateme/2728517#2728517

This seems to be the best resolution as it should match all versions of SQL Server 2008.

Tested update with my web application and it resolved the problem.

public static bool IsSql2008(DataProvider provider)
{
    return provider.DatabaseVersion.Contains("SQL Server 2008");
}
The-Running-Dev commented 14 years ago

Just forked the source and committed back to the master. Should be included in source shortly. This issue is duplicated in issue 8 as well.