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


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.

Fixing the AjaxControlToolkit ColorPicker

March 2, 2012

The Ajax Control Toolkit ColorPicker adds a dynamic color palette to an asp:TextBox and sets the textbox’s value to the hex value of the selected color.

There are two problems with the control (.Net 3.5 build 51116 of Nov 2011):

  • First, the OnClientColorSelectionChanged JS event is not fired if the user edits the textbox manually.

To fix this problem, you need to add two properties to the asp:TextBox declaration

onchange="javascript: colorTextChanged(this);" 
onkeyup="javascript: colorTextChanged(this);"

and implement a JavaScript function

function colorTextChanged(sender) {
    // sender is the textbox, sender.value is the edited color
  • Second, the color palette does not show if you set the SelectedColor property from code

The bug report I found on this topic is quite dated (Sep 2009), so nobody seems to care. The solution provided certainly worked for older versions of ACT, but needs to be adjusted (AjaxControlToolkit vs. Sys.Extended.UI) to look like this:

Sys.Application.add_init(function() {
    // Store the color validation Regex in a "static" object off of   
    // Sys.Extended.UI.ColorPickerBehavior.  If this _colorRegex object hasn't been   
    // created yet, initialize it for the first time.   
    if (!Sys.Extended.UI.ColorPickerBehavior._colorRegex) {
        Sys.Extended.UI.ColorPickerBehavior._colorRegex = 
            new RegExp('^[A-Fa-f0-9]{6}$');