VS – Side-Effects of Upgrading a Project’s .Net Framework Version

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 TABLE #T (id int)    
    SELECT * FROM #T    

which checktsql tries to verify (with the SET FMTONLY ON option) like this


(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:

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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=, 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=, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=, 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 AM
Installing 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 PM
Installing 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 AM
Your 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.
This is a nightmare!

Database Changes Done Right

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!

Checking MS SQL Server Objects

I have written about a method to check stored procedures in MS SQL Server using T-SQL before. I extended this method to also include views and stored functions, and wrote a small utility called checktsql.

The idea is to retrieve all views, stored procedures and functions from an MS SQL Server database, and execute:

  • SELECT TOP(1) * FROM [View]
  • EXECUTE [Procedure]
  • SELECT [Function]
  • SELECT * FROM [TableValuedFunction]

Each statement is executed within a transaction that is rolled back after execution. All expected parameters are set to NULL. All Exceptions are caught and displayed by the application making it easier to find out which objects are out-of-date and need to be fixed.

checktsql supports to modes of operation: plain execution and setting FMTONLY ON. In the latter case, SQL Server checks all conditional branches (i.e. ignores IF/ELSE).

The application opens one tab per connection/check, and displays the result for each object in a list, along with the type of exception and the exception message. The result list can be filtered to the entries containing errors, and be copied to the clipboard using the context menu. (see screenshots)

Some warnings

  • Even though a stored procedure may execute correctly in normal usage, passing NULL parameters may break its functionality and generate error messages like

SQL Exception    Cannot insert the value NULL into column ‘ID’, table ‘mydb.dbo.tablefoo’; column does not allow nulls. INSERT fails. INSERT fails. The statement has been terminated. The statement has been terminated.

This is caused by the NULL values being passed to the procedure.

  • Long-running stored procedures may time out
  • If you have stored procedure that “clean up” tables or reset or destroy data, you should add a dummy parameter if there is none, and distinguish the check mode from real execution by checking the parameter value for NOT NULL.
  • Setting FMTONLY will cause exceptions if the stored procedure uses temporary tables

SQL Exception    Invalid object name ‘#TBL’.

Finally, if you get a message stating

SQL Exception    Invalid object name ‘MyOtherTable’.


SQL Exception    Could not find stored procedure ‘sp_Foo’.

you know where to start to fix your T-SQL code…

checktsql is available for download here.

This is a real version 1.00, so send your comments if something is missing 😉