Wait Wait Don’t Tell Me…..On Second Thought

Consider the following:

Ideally, the example would start at 100 and loop until the counter hits zero, but the statement to decrement the counter is missing. It won’t ever stop running, and there is no visual cue to alert there is an issue. The most logical solution to this would be to add the PRINT statement.

Sadly, this does not solve the issue at hand as the PRINT statement only displays output sent to it after the script finishes executing. It’s great for knowing what occurred after it’s finished but provides no use for telling what is currently happening. Conveniently, there is a solution using the RAISERROR function.

The first parameter is the message to display, and the second and third are the error level and the system state. RAISERROR treats anything with an error level from 0-10 as a message and does not affect code execution. The system state ranges from -1 to 255 and anything outside of that results in an exception (and for the purposes of printing messages, this value is inconsequential). Using RAISERROR and adding the WITH NOWAIT option allows the immediate message output describing how the script is executing as it happens.

informational message

When the error level is raised above 10, Sql Server raises an exception:


This works really well when executing a command from within a .NET application, but an issue arises when executing code from an external process like SqlCmd. When using the RAISERROR function and supplying a severity above 10, it doesn’t always trigger the process’s exit code (Process.ExitCode) to change to something other than 0 which alerts the calling application about an issue.

Exit code 0

Why use SQLCmd when .NET has a built in way of executing code with SqlCommand? The SqlCommand object does not allow the use of the batch seperator: GO. For applications doing Create/Read/Update/Delete (CRUD) operations, not using the GO statement isn’t a problem, but it is important to applications which perform maintenance on databases. Certain SQL execution statementslike CREATE PROCEDURE, must be the first item in a batch meaning chaining multiple statements together in one execution is impossible without using GO.

Fortunately, there is an easy solution to this problem. Change the error level to 20 – 25 or greater and add the WITH LOG option (values greater than 25 are interpreted as 25). This will close the connection to Sql Server and force applications like SqlCmd to return an ExitCode that is something other than 0. Unfortunately to set a severity level between 19-25, the account running the script must either be a member of the sysadmin role or have the Alter Trace permission.

Exit Code Not Zero

It is possible to look at the output and error messages and attempt to parse them and determine if an error occurred instead, but this is error prone and not the standard method for checking for system exceptions. It may work for applications specifically written for this scenario, but applications written to interact with others in the Windows ecosystem look at the exit code to determine process success or failure making setting it to the correct value vital when interacting with different systems.

Mockups – Now with moving pictures

Wireframing applications are great for creating quick visuals of what a screen or series of screens may look like. mockup The quality of the picture compared to the time required to create it is priceless, because no one spends too much time creating a visual display which will ultimately change and be discarded after the team creates the final product.

There are a lot of tools which create quick displays of user interfaces. My personal favorite is from Balsamiq . It’s easy to create mockups of different screens Button Link and Balsamiq has a way of linking mockups together showing the progression. Conveniently there is a presentation mode should someone need to demonstrate it to a group.

The issue with this is that everyone in involved in the decision process still needs a license and know how to use the application or people must meet and those responsible for the design must explain it. Even if everyone is in the same office this still can be a cumbersome task, and furthermore making decisions based on something seen for the first time doesn’t allow one to reflect on what’s presented.

Combing the linked screen ability with video capture software allows you to quickly create videos showing the progression of an application. Although I prefer the SnagIt interface a little better, a program like CamStudio is free and does the job. Now you can email the video or post it to an intranet, and everyone can view it at their convenience and respond at their leisure.