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

September 16, 2013

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:

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 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!

SMOscript and Change Tracking

May 7, 2013

SMOscript user Robert pointed out that SMOscript did not support the Change Tracking option of the ScriptingOptions parameter class.

Indeed, of the new Change Tracking features found in SQL Server 2008 and higher, SMO only supports scripting Change Tracking, but does not generate the commands to script Change Data Capture.

This seems to be a known omission of SMO, and it will not be implemented any time soon:

We took a look at this DCR (this is not defect but actually DCR to add suport of SMO to CDC) along with several others. Unfortunately based on current customers votes count we decided not to proceed with this DCR in the next release. However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this implemented.

which is kind of strange considering you use SMO to re-create an existing database schema in a new schema, and maybe your T-SQL business logic relies on objects created by CDC.

Nonetheless, SMOscript 0.20 now also create the ENABLE CHANGE_TRACKING if used with the -ct switch.

As usual, the latest version of SMOscript is available for download here.


Writing “Funny Characters”? Make sure you use the correct Encoding!

January 14, 2013

A user of SMOscript sent me a bug report:

Some of our stored procedures have apostrophes in comments. If we export our DB and enable the “if not exists” option, our scripts become invalid, as the commented apostrophe ends the string.

He suspected SMOscript might encode the SP code incorrectly, but I made sure that was not the case, so I asked for a (redacted) sample of such a stored procedure.

I added the SP to a database, and in SSMS ran Generate Script on the procedure both with and without the IF NOT EXISTS option. In “plain” mode this simply generates the CREATE PROCEDURE statement, whereas in the IF NOT EXISTS case it will generate the IF NOT EXISTS check, and then create the SP using sp_executesql and dynamic SQL (and some people do not like this very much).

Next, I ran SMOscript with the -i switch, which activates the IF NOT EXISTS mode:

>smoscript.exe -s localhost -d mydatabase -o fooSP -i > fooSP.sql

When I opened the generated file in SSMS, and indeed, the lines that originally contained an apostrophe not contained a quote which ended the string. But I also noted that other quote characters got correctly escaped using 2 quotes.

Then it struck me: the email mentioned apostrophes, but what I saw here was quotes!

I opened the original file in Notepad++ in hex mode, and there it was: e2 80 99, the UTF-8 encoding for U+2019, formally named RIGHT SINGLE QUOTATION MARK, but apostrophe among friends ;)

Given its code point, it is obvious that this character is neither in the ASCII nor in the ASCII character set, so SMOscript has to generate Unicode or UTF-8 encoding.

Fortunately, this functionality is already built-in: use -T for Unicode, or -U for UTF-8 encoding:

>smoscript.exe -s localhost -d mydatabase -o fooSP -i -U -f .\fooSP.sql

Note that I use the -f switch to directly write the output to a file, since the console might not be able to display Unicode characters, or it might, and I’m not smart enough ;) Anyway, directly creating the file from SMOscript frees you from the hassles of piping “funny” characters.

In hindsight, what happened was that SMOscript wrote the output to the console (via piping), and, using the native code page, the console was not able to correctly output the apostrophe character, and replaced it with the quote character, thus breaking the string parameter for sp_executesql.

You should always be aware that if you translate between code pages, not every character can be mapped to a character in the target code page, and some mappings will cause difficulties.

Fortunately, this problem was quite easy to solve.


Database Changes Done Right

March 12, 2012

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!


SMO failing on SQL Azure

March 9, 2012

I was notified that SMOscript fails to generate scripts for SQL Azure databases, see SO here and here.

The reported error message is

Objects in database [DatabaseName] on server [ServerAddress]:

Error:

Index was outside the bounds of the array.

I found reports about SMO access failing on SQL Azure, but I do not have confirmed information whether SP1 of 2008R2 or this Azure SR fix the problem.

If you have information on how to solve this problem, please leave a comment here or answer on SO. Thanks.


SMOscript 0.19

June 26, 2011

There has been a lot of feedback on SMOscript 0.18, and some feature requests are now implemented.

The “script objects” command adds the options “-use” to add a USE [database] command to the generated file, and -fa option to append to existing files.

The “list database” command adds filters for the status of databases: system database, read-only, offline.

Instead of passing the login information using the switches -s, -d, -u, -p, you can now pass a connection string literal (such as used in web.config or app.config) using the -c switch.

New commands  have been added as well:

  • List object dependencies
  • Find string literals
  • Find strings

If an error or exception occurs, the ERRORLEVEL variable will be set (1 for parser errors, 2 to execution errors).

The complete help screen displays like this:

smoscript 0.19.4194.20191 (c) by devio.at 2008-2011

    list and script databases and database objects.

    usage: smoscript [options (leading '-' or '/')] [command]

    connection options:

    -s server       server name
    -d database     database name
    -u username     username (default: integrated authentication)
    -p password     password (if -u is missing, password for sa)
    -c connection   connection string (sets -s, -d, -u, -p)

    scripting options:

    -o [schema.]object  object name

    -r              generate DROP statements
    -i              include IF NOT EXISTS statements
    -use            generate USE [database]

    -f filename     output to file
    -fa             append to file if exists
    -F directory    output to directory

    listing options:

    -l[x]a          list [in]accessible databases
    -l[x]ro         list databases [not] read-only
    -l[x]sys        list [non-] system databases
    -ls             list database status

    dependency options:

    -depth          max. recursion depth
    -dxt            exclude tables in result

    find options:

    -fx string      exclude this string literal (multi)
    -fxs string     exclude strings starting with this literal (multi)
    -fi             find string case-insensitive
    -fc             find string and display code

    output options:

    -A              current ANSI codepage
    -O              ASCII
    -T              Unicode
    -U              UTF8

    commands:

    l           list databases on server (implied by -s)
                list objects in database (implied by -s -d)
    s           script all objects (implied by -s -d -F/-f)
                script single object (implied by -s -d -o)
    db          list database properties
    dep         list object dependencies (-o)
    fs          find string literals
    f string    find string in modules

The latest version of SMOscript is available for download here.


SMOscript 0.18

December 20, 2010

The most recent version of SMOscript fixes an SMO exception caused by duplicate initialization as described in my previous post.

SMOscript is available for download here.


Strange SMO Errors

December 20, 2010

I came across some strange SMO errors today running my SMOscript utility on a new SQL Server 2008 database (10.0.2531.0):

Error: An exception occurred while executing a Transact-SQL statement or batch.

and

Error: Could not find stored procedure ‘msdb.sys.sp_getProcessorUsage’.

A web search did not yield any results.

It turned out that the code was calling Server.Initialize(false) twice, and removing one of the calls caused the program to run without raising exceptions.

Since the last version of SMOscript is dated May 2010, and I have some files created by SMOscript dated July 2010, some magic event must have caused a different behavior in the SMO libraries. I have no idea.


SMOscript 0.17

May 20, 2010

The most recent version of SMOscript is linked to the SMO libraries of SQL Server 2008.

The new version also generates the “GO” statement terminator after each batch.

One would assume this is automatically done by setting (or leaving) the NoCommandTerminator property false. It is not, as discussions on MS Connect show. Ask the Intertubes.

SMOscript is available for download here.


Source Control Management and Databases

March 16, 2010

Source controlling or versioning database schemas is a recurring topic on StackOverflow and on database-related blogs and web sites.

There does not seem to be a single correct solution, rather everybody tries to find their own way to deal with this topic.

The core of the problems seems to be the different types of data between file-based source code and database schemas. Whereas source control systems typically have to deal with source code in files, and every check-in relies on the ability to compile every revision in source control (well, in an ideal world), versioning databases is different in the aspects:

  • store a version of the full database schema (for installation)
  • store a change script / migration script to upgrade from one version to the next (for upgrades)
  • store the values of certain tables (lookup tables, system and application constants)

Command-line tools

In recent years I developed a set of tools that I think may be helpful to the task of versioning databases:

SMOscript is a tool which scripts all database objects in an MS SQL Server database to file using the MS SMO library. Using the -f switch, one file per object is created in a directory. If that directory is under source control, the developer has all CREATE statements in the same revision tool as their application source code.

Oraddlscript is the equivalent of SMOscript for Oracle databases. It makes use of the built-in function DBMS_METADATA.GET_DDL, which provides the CREATE statements for any object inside an Oracle database.

Integrated solution

dbscript is a web application which tries to integrate the 3 aspects mention above:

The generated files can be stored inside the dbscript database (always related to a schema version or a comparison between versions), and / or downloaded to a directory covered by source control.

Note: I am biased, since I use dbscript for the projects I develop since its first stable and reliable version. Actually, dbscript development itself uses dbscript, as can be seen in the SQL files shipped for installation and upgrades.

For more information on the products, see the company website. Free downloads are availabe here.


Follow

Get every new post delivered to your Inbox.

Join 66 other followers