How to script data – generate insert T-SQL statements from existing data

I found this script that is able to generate insert T-SQL statements for data already existing in a database table. The script is packaged into a stored procedure and is thus very convenient to use.
It can reveal very useful when creating scripts for database initialization when packaging the release of a database driven application or moving some data to a new environment.

Developers do not always have access to all the different environments but sometimes still need to package data deployment scripts for those environments. Most often this will apply to lookup tables such as a lookup tables containing configuration values of an application which can potentially change with every release of an application. It could also apply to simpler cases such as a country code lookup table. Anyways, with this script, it will just take you seconds to generate all the insert statements into an SQL script that you can release with other deliverables (libraries, stored procedures…) to the release manager in charge of updating the application on the live environments.

The strong points of this script are that it handles identity column as well as computed columns and can script data for both views and tables. As this script is so useful for me, I backed-up the stored procedure scripts would the original link be one day broken:

I do not have SQL 2008 yet so I could check if the script works for SQL 2008. I guess that it might as SQL Server didn’t change as dramatically between the 2005 and 2008 version as it did between the 2000 and 2005 version. Anyway if someone tries it, please feel welcome to let me know about it in a comment 🙂

Note that commercial products are available would you need fancier data scripting features.

Regular expressions tool designer

Regular expressions are very useful but as I do not have to use them a lot, I often forget the exact syntax required for my particular need.
So today I would like to share a tool to build regular expressions which I have been using many times over, the Regular Expression Designer of Rad Software.

The tool is free, easy to use but yet powerful and intuitive. The contextual help is very short but just perfect and straight to the point for someone who already has previous regex experience. If you are a total newbie in regular expressions, you should read some short introductory material beforehand.

Error configuring BizTalk Server 2006

Today I had quite some troubles installing BizTalk Server 2006. As I have previously installed BizTalk many times without being stuck on a problem for so long, I thought I would blog about it. Who knows, it might help some other soul encountering the same issue.

My configuration was the following:

  • 1 Windows Server 2003 64-bit running SQL Server 2005.
  • 1 Windows Server 2003 64-bit with BizTalk Server 2006 installed but not yet configured.

Both machines had MSDTC and COM+ installed and running, a pre-requisite to install BizTalk on a multiple-server environment.
All the BizTalk Users as well as the Windows Account used to install BizTalk had the necessary rights and belonged to the necessary Groups. See a previous post of mine for more information; Biztalk 2006 multi-server installation guide.

So, after having installed BizTalk Server 2006, the BizTalk Server needs to be configured by using the BizTalk Server Configuration tool and there the weirdest thing happened: it failed, but not completely! Enterprise SSO (Enterprise Single Sign-On) and Business Rules Engine (BRE) components were configured successfully while Group and BizTalk Runtime failed.
Thinking I made some human mistake in the configuration, I retried 2 times but the same problem happened. On a side note, before retrying to configure BizTalk, I had to manually delete the BizTalkMgmtDb as it was created even as the configuration step creating that DB failed.

I then read line by line the log file produced by the configuration tool and searched for the source of the problem. I will paste here the relevant parts:

[1:57:09 PM Error ConfigHelper] [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
[1:57:09 PM Error ConfigHelper] SQL error: 08001 Native error code: 17
[1:57:09 PM Error ConfigHelper] c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(1176): FAILED hr = 80004005
[1:57:09 PM Error ConfigHelper] c:\depotsetupv2\private\common\configwizard\confighelper\sqlhelper.cpp(918): FAILED hr = 80004005
[1:57:09 PM Info ConfigHelper] Did not find existing database: BizTalkMgmtDb on computer:
[1:57:09 PM Info RuleEngineConfig] Management database (server = , database = BizTalkMgmtDb) doesn’t exist

With this information in hand, I suspected something was wrong with the credentials and I checked all the Users and Groups in Active Directory and also checked that I was admin of the SQL Server machine but everything looked ok. I nevertheless re-tried the configuration a few times, being extra careful with every setting but unsuccessfully. I then moved on to next problem in the log file.

[2:08:41 PM Error ConfigHelper] c:\depotsetupv2\private\common\configwizard\confighelper\service.cpp(729): FAILED hr = 80070421
[2:08:41 PM Warning ConfigHelper] The account name is invalid or does not exist, or the password is invalid for the account name specified.
[2:08:41 PM Warning ConfigHelper] Failed to validate service credentials for account: %1
[2:08:41 PM Info BtsCfg] Check Your NT Account Specification

Here the configuration log complains about Windows Credential issues again. Everything was pointing out to Active Directory, this would reveal to actually point me in the wrong direction. So, as I could still not find any problem in AD, I moved on to the next problem I could find in the log:

2008-09-12 14:13:16:0075 [INFO] WMI Deploying ‘C:\Program Files (x86)\Microsoft BizTalk Server 2006\Microsoft.BizTalk.GlobalPropertySchemas.dll’
2008-09-12 14:13:22:0184 [WARN] AdminLib GetBTSMessage: hrErr=80070002; Msg=The system cannot find the file specified.;
2008-09-12 14:13:22:0184 [WARN] AdminLib GetBTSMessage: hrErr=c0c02560; Msg=Failed to read “KeepDbDebugKey” from the registry.
The system cannot find the file specified.;
2008-09-12 14:13:22:0184 [INFO] WMI Error occurred during database creation; attempt to rollback and delete the partially created database’ny-agbdb-301\BizTalkMsgBoxDb’

Two points here:

  • The file “T:\Program Files (x86)\Microsoft BizTalk Server 2006\Microsoft.BizTalk.GlobalPropertySchemas.dll” was present on the file system so it didn’t seem there was anything I could do about it.
  • The key “KeepDbDebugKey” did not exist in the registry of running healthy BizTalk systems so I supposed this registry key exists only temporarily during the BizTalk configuration procedure.

I then moved on the next potential problem in the log file:

2008-09-12 14:14:02:0465 [ERR] WMI Failed in pAdmInst->Create() in CWMIInstProv::PutInstance(). HR=c0c025b3
2008-09-12 14:14:02:0465 [ERR] WMI WMI error description is generated: Exception of type ‘System.EnterpriseServices.TransactionProxyException’ was thrown.

Here the configuration tool log entry complains about a Transaction from the System.EnterpriseServices library. As this package refers to Distributed Transactions (MS DTC), I doubled checked that MS DTC was running well on both machines, checked firewall configurations but all the necessary ports were open.
I then started to try to troubleshoot DTC following the MSDTC troubleshooting guide from Microsoft and found a utility called dtctester that can be downloaded from the KB 293799.

The dtctester test failed and so I was now sure that the problem was related with DTC. I re-checked the firewall settings, checked the dual network adapter and the DNS as advised by Microsoft and found out that the problem lied with the DNS having some wrong reverse mappings (IP address -> hostname mappings).

As a corollary, notice that the only log information that proved to point me to the actual source of the problem were log entries with the string [ERR] in their header. All others had either [INFO] or [WARN].

To conclude, I would advise to:

  • Use dtctester before operating a BizTalk multi-server installation.
  • When running into problems, look first at the log entries having either the [ERR] or Error string in their header – It indeed seems there are two different formats of log entry header in the log file produced by the Configuration tool. While looking first for the Error type of log entries sounds obvious, it seems that not many people are aware of the log entry header formats and the log entry types generated in the log file.

How to purge BizTalkMsgDb?

If the BizTalk Message Box database (BizTalkMsgDb) grows too large, performance of the database subsystem will reduce. As a rule of thumb, the BizTalkMsgDb should never grow larger than 5 Gb for large systems with long running transactions. For high-volume environment with no long running transactions, the BizTalkMsgDb size should be much smaller than 5Gb.

Would your message box have already grown too large and the system become unresponsive, you might want to clean up the BizTalkMsgDb database manually but keep in mind that:

  • The BizTalk Server must be taken down during the procedure.
  • All existing messages will be purged and lost.

There is an article in the MSDN documentation that explains in details how to manually purge data from the Message Box Database.

To make a long story short, here is step by step summary:

  • Stop ALL BizTalk service host instances from the Services console.
  • Restart IIS by running IISRESET from the command prompt if you are running any adapters in isolated hosts (for example HTTP, SOAP, or WCF).
  • Execute the stored procedure bts_CleanupMsgbox on your message box database. If the stored procedure does not exist, create it by running the sql script “msgbox_cleanup_logic.sql” found in the directory “\Schema\”.
    Important note: If you are running BizTalk Server 2006, you first have to get an updated version of the Msgbox_cleanup_logic.sql file to (re)create the bts_CleanupMsgbox stored procedure at http://support.microsoft.com/kb/924715. The hotfix is available for download directly without having to contact MS. If you are running BizTalk Server 2006 R2 then the updated version of the Msgbox_cleanup_logic.sql file is already installed and you do not need to download the hotfix.
  • Execute the stored procedure bts_PurgeSubscriptions on your message box database. If the stored procedure does not exist, you can create it by inspecting the sql script “msgboxlogic.sql” found in the directory “\Schema\”. Copy paste only the part of the script that creates this specific stored procedure and run it.

As deleting data in a database does not reduce the size the database files on the disk; you need to shrink the database files if you want to reduce its physical size. There are 2 simple ways to do it:

  • Through SQL Server Management Studio, right click on the BizTalkMsgDb database, click on Tasks > Shrink > Database
  • Through T-SQL, using the DBCC SHRINKDATABASE command: DBCC SHRINKDATABASE (BizTalkDTADb);

Microsoft does not support this procedure on production systems simply because this will purge production data. So, as long as you keep in mind that all existing messages will be purged, it is safe to run it on a production environment.

Truncating the Database Log File:
There is also another useful command to truncate the database logfile, would it be necessary (such as running out of disk space):
BACKUP LOG BizTalkDTADb WITH TRUNCATE_ONLY (*)
Note that you are not supposed to do this on production but emergency scenarios can call for the necessity.

(*) Edit:
The T-SQL BACKUP command does not support the TRUNCATE_ONLY parameter since SQL Server 2008. See T-SQL BACKUP Reference.
As explained in this post, it was discontinued because it breaks the ability to recover from a certain point in time until a full backup is run again. It was intended to be used only in case of emergency such as when we run out of disk space but apparently it was regularly misused by DBAs and maybe the reason was that it was a misunderstood option of the BACKUP command. It can indeed be a little misleading that an option of the BACKUP command can “break” the backup.
The way to truncate the log file on SQL Server 2008 and above in case of emergency is the following:

  1. Set the Database in Simple Recovery mode.
  2. Shrink the log file.
  3. Set the Database back into Full recovery mode.

Example taken from DBCC SHRINKFILE T-SQL reference:
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

How to Handle Unhandled Exception

When an unhandled exception occurs in the .Net Framework run-time 1.0 & 1.1, the Windows Process will terminate only if the exception occurred on the main application thread. Would an unhandled exception occur in a thread other than the main application thread, it will be caught by the runtime and therefore NOT cause the application to terminate. This means that unhandled exceptions in child threads disappear silently without anyone being able to know about it, implying that bugs are silently swallowed by the runtime without anyone being notified about it. The dangerous scenario is that a user might believe that a unit of work has executed successfully while it is actually not the case. This is in my opinion something not desirable and has been addressed in later version of the .Net Framework.

Indeed, from the .Net Framework 2.0 onwards, unhandled exceptions occurring on ANY thread (thus including any child threads being background threads or not) shuts down the application running the particular thread.

When the runtime terminates an application because of an unhandled exception, it writes an entry in the Windows Event Log which looks like the following:

EventType clr20r3, P1 processname, P2 1.0.0.0, P3 485f85f0, P4 system, P5 2.0.0.0, P6 471ebf0d, P7 3832, P8 bf, P9 system.componentmodel.win32, P10 NIL.

“processname” being the name of the .Net executable.

1. How to resolve unhandled exceptions? The System.AppDomain.UnhandledException event.

If an unhandled exception occurs in a windows application (multi-threaded or not), a windows service named winservice.exe for example, the runtime will terminate the service and write an Event Log entry such as:
“EventType clr20r3, P1 winservice.exe, P2 1.0.0.0, P3 485f85f0, P4 system, P5 2.0.0.0, P6 471ebf0d, P7 3832, P8 bf, P9 system.componentmodel.win32, P10 NIL.”

While it is good to be notified that a service crashed, the information supplied is rather cryptic and has very little added value. It would be nicer to have a way to log unhandled exceptions with a more meaningful message so that it is possible to get enough information to be able to fix the source code and re-deploy the application.

Conveniently, the .Net Framework provides the System.AppDomain.UnhandledException event. This event fires whenever an application domain unloads because of an unhandled exception. Note that registering for the event does not cause unhandled exceptions to be “handled”. It simply notifies you that an exception has gone unhandled, so that it is possible to take some action such as logging the exception message and stack trace and eventually do some clean up before the application dies. The Exception is still unhandled and so still causes the application to shut down.

The discussion in this article is not a solution on how to handle exceptions. Exception handling is a strategy on how to handle exceptions and is a different discussion. This article explains how to act with unhandled exceptions so that details about the exception can be logged so that a proper solution can be found to resolve the issue/bug.

Ideally, all exceptions should be handled in the source code with try-catch-finally blocks so that unhandled exceptions do not occur. Nevertheless, there are always unforeseen cases and developer mistakes which, in my opinion, justify caring for unhandled exceptions across the board.

Here is an example on how to register for the event with an event handler that log exception details in the Windows Event Log:

// This event provides notification of uncaught exceptions. Write this in the entry point of your program, like in the OnStart() method of a Windows Service.
AppDomain currentDomain = AppDomain.CurrentDomain;
currentDomain.UnhandledException += new UnhandledExceptionEventHandler(UnhandledExceptionHandler);
static void UnhandledExceptionHandler(object sender, UnhandledExceptionEventArgs args)
{
Exception ex = (Exception)args.ExceptionObject;
EventLog.WriteEntry(“WinService.exe”, “Unhandled Exception caught: ” + ex.Message + ex.StackTrace, EventLogEntryType.Error);
}

2. Note for Windows Form Applications – Application.ThreadException event.

2.1 Application.ThreadException event

For Windows Form Applications, there is another event that is raised when an unhandled exception occurs, the System.Windows.Forms.Application.ThreadException. Nevertheless, this event fires only when unhandled exceptions happen in Window Forms threads (UI threads). This means when an exception is thrown from code that was ultimately called as a result of a Windows Message. Windows Messages are emitted by keyboard hits, mouse clicks or “paint” messages,… in short, nearly all code in a typical Windows Forms application.

While this works perfectly, it lulls one into a false sense of security that all exceptions will be caught by the central exception handler:

  • Exceptions thrown on worker threads are a good example of exceptions not caught by Application.ThreadException.
  • Exceptions thrown by the code inside the Main method of the Windows Forms application, including the main form’s constructor, executes before the Windows message loop begins and so is another example of exceptions that do not raise the Application.ThreadException event.

In this case, we said the Application.ThreadException event handler to be a “central exception handler” because it is still possible for the application to keep running when this event is raised, depending on what kind of logic is implemented in the handler.

As a reminder, Worker Threads are threads:

  • Created manually: Thread.Start()
  • Created by the ThreadPool: ThreadPool.QueueUserWorkItem()
  • Created by any kind of asynchronous call which internally uses a thread pool thread to execute: Delegate.BeginInvoke(), BeginXXX()

One must attach a handler to the Application.ThreadException event before instantiating the main form of the application by calling Application.Run(). Also, because this is a static event, you must detach the event handler(s) when the application is disposed or memory leaks will result.

The Application.ThreadException as a default event handler which behaves in the following way:

  • If an unhandled exception occurs in the main application thread, the default exception handler catches it and terminates the application.
  • If an exception occurs in a thread other than the main application thread, the thread exits, but the application continues to run.

2.2 Application.SetUnhandledExceptionMode

It is possible to instruct the application whether it should catch all unhandled exceptions thrown by Windows Forms components and terminate the application, or whether it should raise an event so that an event handler can be implemented; the event handler could halt execution and expose the unhandled exception to the user. This is setting is done by using the application configuration file or the Application.SetUnhandledExceptionMode() method.
It is possible to instruct the application whether it should catch all unhandled exceptions thrown by Windows Forms components and terminate the application, or whether it should raise an event so that an event handler can be implemented; the event handler could halt execution and expose the unhandled exception to the user. This is setting is done by using the application configuration file or the Application.SetUnhandledExceptionMode() method.

  • UnhandledExceptionMode.ThrowException never route exceptions to the Application.ThreadException event handler and so the default event handler will terminate the application when an unhandled exception occurs as explained earlier.
  • UnhandledExceptionMode.CatchException always route exceptions to the Application.ThreadException event handler.

Again, as a reminder, the Application.ThreadException event handler is only for unhandled exception occurring on UI threads and so the SetUnhandledExceptionMode() method affects only the way unhandled exceptions coming from UI threads are treated, it does not affect how non UI threads unhandled exceptions are treated. System.AppDomain.UnhandledException event handlers will always be called when non UI threads unhandled exceptions occur.

2.3 Sample

Hereunder is a code sample on how to register to the Application.ThreadException event in a Windows Form Application. As said earlier the event handler will only be called for unhandled exceptions occurring on the UI thread. Thus, the code sample also has an event handler for unhandled exception on non-UI threads by registering to the System.AppDomain.UnhandledException event:

static class Program
{
///

/// The main entry point for the application.
///

[STAThread]
static void Main()
{
// Add the event handler for handling UI thread exceptions to the event.
Application.ThreadException += new ThreadExceptionEventHandler(Form1_UIThreadException);

// Set the unhandled exception mode to force all Windows Forms errors on UI threads
// to go through our handler regardless of application settings.
Application.SetUnhandledExceptionMode(UnhandledExceptionMode.CatchException);

// Add the event handler for handling non-UI thread exceptions to the event.
AppDomain.CurrentDomain.UnhandledException +=
new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);

// Default generated code by Visual Studio for WinForms
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}

You can find a zip file here containing a complete Visual Studio 2008 solution demonstrating both type of unhandled exception event handler and their effect.

3. Note for ASP.NET applications – Application_Error in Global.asax.

Would an unhandled exception occurs in an ASP.NET application on a worker thread, the runtime will terminate the worker process (w3wpp.exe) and write an Event Log entry such as:
“EventType clr20r3, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435be1, P4 app_web_7437ep-9, P5 0.0.0.0, P6 433b1670, P7 9, P8 a, P9 system.exception, P10 NIL.”

In ASP.Net, there is an Application_Error method in the Global.asax file that can be implemented so that an action can be taken when an unhandled exception occurs (logging the exception details, for example). Again, treating an unhandled exception in such a way is not handling an exception (that should be done in the try-catch-finally block in the code), the web application will still crash and display an error message on the browser but it lets the programmer have a way to log information about the exception and eventually do some other clean-up tasks.

Nevertheless, similarly to Windows Forms Application, the Application_Error will be called only for unhandled exception that occurred on the main thread. Unhandled exceptions occurring on worker threads will not be caught by the Application_Error event handler in the global.asax file. If we want to be notified of unhandled exception occurring on worker threads, we need to register an event handler to the System.AppDomain.UnhandledException event, as we did for Windows Services and Forms Applications. The event handler should at least log the exception and eventually do some clean tasks.

Microsoft has a KB on how to implement an event handler for the System.AppDomain.UnhandledException event within an HTTPModule.

You can find here a zip file with a Visual Studio 2008 solution using the HTTPModule defined in the KB. I registered the HTTP module in the web.config the following way:



.Net assembly is said to have a strong name when it is signed.

There are 2 ways to retrieve the Public Key Token from a strongly named assembly:

  • The lazy way is to add the assembly in the GAC and go to C:\WINDOWS\assembly, one of the column showed in Windows Explorer is the Public Key Token. Only strongly named assemblies can be stored in the GAC.
  • Run the command line “sn -T UnhandledExceptionModule.dll” which prints the Public Key token of a strongly named assembly.

Contrary to the instructions given in the KB, I did not GAC either NGEN the HTTP module; I just referenced the HTTPModule project from the web project so that the HTTP module library is automatically copied to the web application bin folder. All assemblies in the bin folder are automatically found by the run-time so that is not necessary to GAC all third party libraries.