The other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into SMOscript.
Now, up to the current versions, both applications have been developed using Visual Studio 2008 targeting .Net 2.0. (I prefer to keep requirements to a minimum). But now I thought it was time to migrated them to VS2010/3.5 (client framework), and came across a couple of unresolved mysteries.
Missing Exceptions in SqlCommand.Execute*()
The first mystery is that the behavior of the Execute* methods changed such that ExecuteNonQuery() and ExecuteReader() do not reliably raise exceptions any more if an error occurs in T-SQL.
Take the example
CREATE PROCEDURE TestTemp AS CREATE TABLE #T (id int) SELECT * FROM #T GO
which checktsql tries to verify (with the SET FMTONLY ON option) like this
BEGIN TRAN SET FMTONLY ON; EXECUTE [dbo].TestTemp ROLLBACK
(Note that the transaction is actually created by SqlConnection.BeginTransaction(), but essentially it’s the same mechanism)
SSMS returns an error:
Msg 208, Level 16, State 0, Procedure TestTemp, Line 6 Invalid object name '#T'.
What I noticed after migrating the code to VS2010/.Net3.5 is that some (most?) stored procedures cause the error to be raised as an exception in .Net, and some do not. However, the behavior is consistent for each stored procedure.
Fortunately, I noticed the different behavior only when using the SET FMTONLY ON option, but still, there is a difference depending on which VS or framework version is used.
Teh internets did not really help me – I found a couple of discussions, but no documentation of the change, or how to get the original semantics back:
- How can I get an error message that happens when using ExecuteNonQuery()?
- SqlCommand.ExecuteReader() not throwing Exception on erroneous TSQL-Statement
- RAISERROR within CATCH not propagated to C# Caller
SMO dependency on System.Core
When I tried to debug (and understand) above issue, and reverted the application back to .Net 2.0, but still build with VS2010, I suddenly got the error message during build:
The primary reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089” which could not be resolved in the currently targeted framework. “.NETFramework,Version=v2.0”. To resolve this problem, either remove the reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”
There was nothing I could do to get VS2010 to the original behavior (i.e.: compile successfully) targeting .Net 2.0, even though the same assemblies were referenced both in the VS2008 and the VS2010 project.
This thread on MS connect shows the “amusing” dependency zigzag that SMO assemblies implement, switch dependencies even between minor releases:
Posted by Chris Dennis on 4/20/2011 at 10:19 AMInstalling SQL Server 2008R2 Cumulative Update 7 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1777.0 which again depends on System.Core v3.5.
Posted by Chris Dennis on 3/25/2011 at 12:50 PMInstalling SQL Server 2008R2 Cumulative Update 6 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1765.0 which does not depend on System.Core v3.5. Only version 10.50.1750.9 has a dependency on System.Core v3.5.
Posted by Microsoft on 3/25/2011 at 11:08 AMYour assertion is correct the Microsoft.SqlServer.Management.SqlParser took a dependency on System.Core v3.5. Any projects which reference this assembly directly or indirectly need to be targeted to .NET Framework V3.5.
devioblog 