Monday, November 13, 2006

Passed 70-431

This Saturday, I passed my first exam in the new certification series: TS: Microsoft SQL Server 2005 - Implementation and Maintenance (70-431).

This is so far the hardest exam I took. Don't get me wrong. I have taken 5 Microsoft exams before and one of them is exam 70-229 using SQL Server 2000. However, since my background is more as an application developer rather than a database administrator, I found the exam very challenging. Unfortunate for me, it just cover a little bit of TSQL programming, the material that I am familar most. Most of the exam material focuses on the administrative/maintenance tasks such as backup/restore, indexing, and various high availability technologies.

Lucky for me, I still managed to score 947 out of 1000.

Here are few useful tips for the exam takers:

  • If you're unfamiliar with the administrative/maintenance stuff, get a good book that explain the concepts rather than just giving you facts. By learning this way, you don't need squeeze your brain to understand all the new terminology. I highly recommend the MCTS Self-Paced Training Kit (the 'blue' book). Find out about the book here.
    I also read the Exam Cram (the 'red' book), but it is not that helpful for me.
  • Do some practice. Follow the practice/lab from the book (if any). I find that doing practice help me to grasp the concept quicker. I prefer to see things get executed than just believe what the author write in the book. Not convinced yet? Go to the next point
  • Expect to see simulation questions... and a lot of them. So you really need to practice.
  • Download the latest SQL Server Book Online and read additional material not covered in the book, but don't spend too much time studying this manual. Admit that you don't have time and energy to study everything. It's OK to miss one or two difficult questions, but don't miss the easy ones because you don't have time to study that chapter.
  • Still related to the previous point... Don't try to remember all the switches, parameters, options, etc. from a SQL syntax. They are just too many... Only remember the important ones. How to identify the important ones? Read the book. If a very detail question appears on your exam and you really have no idea, use your intuitive or skip the question for later review. You may find the clue in the other questions.
  • As always, Microsoft is proud of the new technologies/features, so put emphasizes on the new features, such as XML data type, database mirroring, and some enhancements in the TSQL syntax.
  • Write on a piece of paper (OK you can use Notepad) all the important dynamic management views and functions. Read the note later when you are about to enter the exam room.

Hope this helps.

Tuesday, October 17, 2006

Scripting SQL Server database with ScriptTableData.cst

Recently, I worked on SQL scripts for database deployment. I use VS.NET 2003's Database Project to maintain the Create Script and Change Script in the SourceSafe. Those scripts will be run automatically by the build tool (NAnt) to create the database from scratch at any time and update the existing database as required.

The database project in VS.NET is an excellent tool to script database objects like tables, views, UDFs and stored procedures. You just need to drag and drop those objects from the Server pane to the Solution pane and automatically all the required SQL scripts will be generated and added to the solution. However, it fells short when I use the database project to script the data. The data is stored as binary file, so I lose control of it.

Surprisingly, Codesmith 2.6 (the free edition) comes with a template called ScriptTableData.cst that will do exactly what I want: to script the data as SQL statements. It produces INSERT INTO... SELECT statements that can be run on the target database to populate the data.

Unfortunately, the ScriptTableData template contains some annoying bugs. For example, the bit data type is scripted as "true" or "false" instead of "1" or "0", causing errors when you execute the produced SQL script. Moreover, it does not handle datetime and binary datatype very well.

I decided to modify the script to help my work. The following is the modified version of ScriptTableData.cst. All good credit goes to the maker of this excellent code generator, Eric J. Smith. I only fix some bugs and add few more features.

Bugs fixed:

  • Boolean data type is handled properly
  • SET IDENTITY_INSERT appears only for the table with identity column


  • More precise representation for DateTime data type
  • Support binary data type
  • Support two style of scripting: Compact and Verbose. The verbose style will produce SQL script similar to the commercial tools.

The template:

<%@ CodeTemplate Language="C#" Debug="True" TargetLanguage="T-SQL" Description="Generates a script based on the data from a table." %>
%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="\tContext" Description="Table to get the data from." %>
%@ Property Name="ScriptType" Type="ScriptTypeEnum" Category="Option" Default="Compact" Description="How the script is rendered" %>

%@ Assembly Name="SchemaExplorer" %>
%@ Assembly Name="CodeSmith.BaseTemplates" %>
%@ Assembly Name="System.Data" %>
%@ Import Namespace="SchemaExplorer" %>
%@ Import Namespace="System.Data" %>
%@ Import Namespace="System.Text" %>
%@ Import Namespace="System.Collections" %>

hasIdentity = HasIdentityColumn();
string tableName = string.Format("{0}.[{1}]", GetTableOwner(), SourceTable.Name);
columnList = GetColumnList();
USE <%= SourceTable.Database.Name %>

% if (hasIdentity) {%>
DBCC CHECKIDENT ('<%=tableName%>', RESEED, 1)
<%=tableName%> ON

% if (ScriptType==ScriptTypeEnum.Compact) { %>
INSERT INTO <%=tableName%> (<%=columnList%>)
<% for (int i = 0; i < SourceTableData.Rows.Count; i++) { %>
SELECT <%= GetTableRowValues(SourceTableData.Rows[i]) %><% if (i < SourceTableData.Rows.Count - 1) { %> UNION<% } %>
<% } %>
%} else { for (int i = 0; i < SourceTableData.Rows.Count; i++) { %>
INSERT INTO <%=tableName%> (<%=columnList%>) VALUES (<%=GetTableRowValues(SourceTableData.Rows[i])%>)

% if (hasIdentity) {%>
script runat="template">

public enum ScriptTypeEnum

private DataTable _sourceTableData;

private DataTable SourceTableData
if (_sourceTableData == null)
_sourceTableData = SourceTable.GetTableData();

return _sourceTableData;

public string GetColumnList()
ArrayList columnList = new ArrayList(SourceTable.Columns.Count);
foreach(ColumnSchema column in SourceTable.Columns)
columnList.Add(string.Format("[{0}]", column.Name));
return string.Join(", ", (string[]) columnList.ToArray(typeof(string)));

public string GetTableRowValues(DataRow row)
StringBuilder rowBuilder = new StringBuilder();

int columnCount = SourceTable.Columns.Count;
ArrayList valueList = new ArrayList(columnCount);
for (int i=0; i<columnCount; i++)
ColumnSchema column = SourceTable.Columns[i];
if (row[i] == DBNull.Value)
switch (column.NativeType.ToLower())
case "bigint":
case "decimal":
case "float":
case "int":
case "money":
case "numeric":
case "real":
case "smallint":
case "smallmoney":
case "tinyint":
// numeric type

case "bit":
// boolean type
string val = ((bool) row[i]) ? "1" : "0";

case "varbinary":
case "binary":
// binary type

case "datetime":
case "smalldatetime":
// datetime type
DateTime dt = (DateTime) row[i];
valueList.Add(string.Format("'{0:yyyy-MM-dd HH:mm:ss.fff}'", dt));

// other type
valueList.Add(string.Format("'{0}'", PrepareValue(row[i].ToString())));
return string.Join(", ", (string[]) valueList.ToArray(typeof(string)));

string PrepareValue(string value)
return value.Replace("'", "''").Replace("\r\n", "' + CHAR(13) + CHAR(10) + '").Replace("\n", "' + CHAR(10) + '");

string GetTableOwner()
string owner = SourceTable.Owner;
if (!owner.Equals(string.Empty))
return string.Format("[{0}]", owner);
return string.Empty;

string GetHexStringFromBytes(byte[] bytes)
if (bytes == null)
return string.Empty;

int byteCount = bytes.Length;
StringBuilder sb = new StringBuilder(byteCount * 2 + 2);
for (int i = 0; i < byteCount; i++)
return sb.ToString();

bool HasIdentityColumn()
foreach(ColumnSchema column in SourceTable.Columns)
if (column.ExtendedProperties["CS_IsIdentity"].Value.ToString()=="True")
return true;
return false;


Friday, October 06, 2006

Detecting user authentication expiration from an AJAX request

How to detect that an AJAX request has been redirected to a login page by the ASP.NET's Forms Authentication?

Forms Authentication is a standard user authentication is ASP.NET. As a refresher, this authentication scheme utilizes cookie to track whether a user already login. A user requesting a secured page will be redirected to a login page if he/she is not authenticated yet. Once the user is authenticated, the server will redirect to the page that the user originally request.

The Forms Authentication works fine for normal ASPX pages. When the user is idle for a certain amount of time (the default is 30 minutes), the cookie is expired, and the user will be forced to relogin again.

In an AJAX application, the server also behaves the same way. When the cookie is expired, the server will automatically redirect to the login page. However, since the request is made through a XmlHttpRequest object, the browser does not automatically load the login page, instead the content of login page is retrieved by the XmlHttpRequest object.

I use a HTTP custom header to differentiate a login page from other pages in the web application. By doing this way, Javascript can easily identify whether an AJAX request has been redirected to a login page (Well... it actually detects whether it receives the login page instead of the expected response).

In the code behind of the login page, add the following code:

// add a custom HTTP header to identify that this is a login page

In the Javascript code, in the function that handles XmlHttpRequest's response, add the code in bold:

xmlHttp.onreadystatechange = function() {
if (xmlHttp.readyState==4) {
if (xmlHttp.status==200 && xmlHttp.responseText!=null) {
if (xmlHttp.getResponseHeader('IsLoginPage')=='1') {
alert('Your session has expired. Please relogin again');

} else if (typeof(responseHandler)=='function') {

I decided to simply display an alert so that the user is aware of the situation. My expectation is upon receiving this message, the user will explicitly relogin to the application.

Tuesday, October 03, 2006

Data Encryption in SQL Server 2005

One of our product stores sensitive information in the database. Our client wants the sensitive information to be encrypted so that nobody except the authorized person can access to it.

We want to implement the data encryption at the database level so that it becomes transparent to the applications using the data. The developer should not need to take care about data encryption/decryption and equally important our reporting system which is based on Reporting Service should still works.

SQL Server 2000 does not have built-in encryption capability. Data encryption can be done by using extended stored procedures that utilize external dll. This is definitely cumbersome.

It turns out that data encryption is a native feature in the new SQL Server 2005. So without further ado, I spent some time investigating the data encryption feature.

In SQL Server 2005, data can be encrypted using symmetric keys, asymmetric keys, certificates, or passphrases (plain text), being the last option as the least recommended. We can use a combination of several encryption mechanisms to create a hierarchy of encryption. For example, the data is first encrypted using a symmetric key, then the symmetric key is encrypted using an asymmetric key, and so on to make a stronger encryption.

Encryption using certificates and asymmetric keys are slower but more secure than using symmetric keys. Microsoft recommends using symmetric key to encrypt large amount of data and then secure the symmetric key by asymmetric keys or certificates.

The code following this blog entry is my first attempt to test the encryption in SQL Server 2005. I created a hypothetical Employee table that stores salary (as money data type), credit card number (varchar) and birth date (datetime). Since encrypted data is stored as varbinary, those columns are declared as varbinary instead of their original type.

Following Microsoft's recommendation for large amount of data, I use a symmetric key to encrypt/decrypt the data. The symmetric key is then encrypted by a certificate created internally in SQL Server. Since I don't specify any further encryption mechanism to secure the certificate, by default, the certificate is encrypted by the database master key. In the encryption hierarchy, the database master key is further encrypted by the service master key, and the service master key is secured by DPAPI at the operating system level.

I use EncryptByKey and DecryptByKey functions for the encryption/decryption. These functions only accept varchar, nvarchar, char, nchar, varbinary and binary data type. For other data type like datetime and money, we need to CAST/CONVERT it to varbinary. The symmetric key must also be opened/decrypted before we can use it for encryption/decryption.

In the view, I use DecryptByKeyAutoCert that automatically opens the symmetric key and uses it to decrypt the cipher text.

The code:

-- create database
-- use database
USE EncryptionTest
-- create master key for the new database
-- Create table
CREATE TABLE [dbo].[Employee](
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
       [Name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       [Position] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
       [Salary] [varbinary](256) NOT NULL,
       [CreditCard] [varbinary](256) NULL,
       [BirthDate] [varbinary](256) NULL,
       [EmployeeID] ASC
-- create certificate to encrypt the symmetric key
       WITH SUBJECT = 'Company Certificate',
       START_DATE = '1/1/2006',
       EXPIRY_DATE = '12/31/2006';
-- create symmetric key and encrypt it with the certificate
-- create a view to access Employee table
CREATE VIEW [dbo].[vw_Employee]
SELECT  [Name],
              CONVERT(MONEY, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, Salary)) AS Salary,
              CONVERT(VARCHAR, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, CreditCard)) AS CreditCard,
              CONVERT(DATETIME, DecryptByKeyAutoCert(CERT_ID('EmployeeCert'), NULL, BirthDate), 112) AS BirthDate
FROM dbo.Employee
-- *** Batch: Encryption
-- open symmetric key
-- get symmetric key id to be used in the encryption
DECLARE @KeyGUID uniqueidentifier
SET @KeyGUID = KEY_GUID('EmployeeKey')
-- insert some records to Employee table
INSERT INTO dbo.Employee([Name], Position, Salary, CreditCard, BirthDate)
       'John Smith',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $200000)),
       EncryptByKey(@KeyGUID, '4444-3333-2222-1111'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19400502', 112)))
       'Garry Baker',
       'General Manager',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $150000)),
       EncryptByKey(@KeyGUID, '4444-3333-2211-1144'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19450108', 112)))
       'Natasha Smith',
       'Account Manager',
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), $120000)),
       EncryptByKey(@KeyGUID, '4444-1111-1111-1111'),
       EncryptByKey(@KeyGUID, CONVERT(VARBINARY(256), CONVERT(DATETIME, '19550501', 112)))
-- close key
-- *** Batch: Decryption
-- open symmetric key
-- select Employee
SELECT  [Name],
              CONVERT(MONEY, DecryptByKey(Salary)) AS Salary,
              CONVERT(VARCHAR, DecryptByKey(CreditCard)) AS CreditCard,
              CONVERT(DATETIME, DecryptByKey(BirthDate), 112) AS BirthDate
FROM dbo.Employee
-- close key

Tuesday, September 05, 2006

Javascript Autocast

A typical day at work requires me to constantly switch between writing code in C# and Javascript. This effects the way I write code in both languages. Since C# is more strict that Javascript, my coding style leans towards C# and sometimes I find myself writing Javascript code in C# style, which can be viewed as good or a bad thing.

Every language has its goodies that if we take advantage of can produce better quality code.

Let's take a common task as example. We want to test a string variable for null and empty.

In C#,I will do like:

string str;

if (str != null && !str.Equals(string.Empty)) { }

Applying the same C# style to Javascript will result in the following code:

var str;
if (str != null && str != '') { }

The above code is not optimized. In Javascript, we can utilitize 'autocast' feature that will reduce the code into:

var str;
if (str) { }

A null or an empty string autocasts into false, and therefore the condition is evaluated as false.

As you can see, the code is much shorter and cleaner. Size does matter in Javascript. Shorter code translates into faster download time.

Autocast also applies to other types/conditions:

  • Undefined variable is evaluated as false. A variable is undefined if it hasn't been assigned a value.

    var myVar;
    if (myVar) { } // false

  • Empty string is evaluated as false

    var myStr = '';
    if (myStr) { } // false

    var myStr2 = 'ABC';
    if (myStr2) { } // true

  • Zero is evaluated as false

    var myNum = 0;
    if (myNum) { } // false

    var myNum2 = 1;
    if (myNum2) { } // true

  • Null is evaluated as false

    var myObj = null;
    if (myObj) { } // false

  • Object is evaluated as true

    var myObj = {};
    var myObj2 = new Object();
    if (myObj) { } // true
    if (myObj2) { } // also true

  • Empty array is evaluated as true
    An array is essentially an object, therefore it is evaluated as true.

    var myArray = [];
    var myArray2 = new Array();
    if (myArray) { } // true
    if (myArray2) { } // true

Friday, September 01, 2006

Javascript optimization

The Internet Explorer Team (the team that brings us IE 7) has posted a nice article about Javascript optimization. This is the first part of the scheduled 3 parts article. In this article, the main drive behind the optimization is to reduce the number of symbolic lookups made by the Javascript engine to map the variable name to its real object.

It is quite rare to find such optimization tips from the maker of the browser. I am glad finally IE team pays some attention to improve the Javascript development. We need more articles like this, as more and more Javascript code is written nowdays. In my current project which is using ASP.NET 1.1, about 75% of the UI code is written in Javascript, and the rest is the C# code behind.

Please find the article here

Tuesday, August 29, 2006

Know your end users

On a Saturday morning, I went to a bank to do some over the counter transaction. I was attended by a customer service staff who used a Dell desktop computer with an LCD monitor.

My request takes many forms to be filled and a lot of data entries to be made to the system. I couldn't see the monitor, but I just imagine by looking at how busy the female banker was entering data using the keyboard. Surprisingly, she didn't use the mouse at all and rely entirely on the keyboard and its function keys (F1-F12). The mouse is connected to the computer, but she put it in front of the keyboard so I believe she just want to make some space by getting rid of it.

On another ocassion in a travel agency, I notice a diffent balance between keyboard and mouse usage. I was booking for an airline ticket and the staff attending me used both keyboard and mouse. However, he used the keyboard much more frequently to enter not-so-user-friendly commands on the terminal window and only few times used the mouse to click on the big toolbar located on top of the terminal window. Perhaps the toolbar is used to execute a simple command like 'Print Flight Itinerary'.

Drawing from the two short scenarios above, we can see that different users have different way to use the application. Naturally, the platform of an application defines its limitation, like in a terminal window where everything is text, keyboard is definitely the main input device. However, in most today's applications, desktop-based or web-based, the mouse and the keyboard are both acceptable input devices. But still many people choose to mainly use keyboard alone. They do have valid reasons, most probably because they are so familiar with the keyboard and therefore can operate faster compared to using the mouse.

It is paramount for us, the software developers, to know the behaviour of the end users who will actually use the application we build. Imagine if we develop a cool interactive web application, fully enriched with DHTML popups, animations, and drag and drops, only to realize later after the release that the users prefer to navigate using combinations of keyboard arrows and tabs rather than the mouse.

I highly recommend developers, who spent most of the time behind the stage, to come out from their cubicle and pay a visit to client office. Look at how your end users use the application that you build. I bet you will be surprised and it may change the way you design and develop your application.