To remove “Is Identity” setting in SQL Server: Not simple at 10:37 AM

Yesterday I found a seems-like-simple SQL problem: It is a simple task inside SQL Server Enterprise Manager (Management Studio), but there is no simple single SQL statement to set “Is Identity” to “no” for a table column. Because the setting is not a constraint, “alter table” statement does not work.

It turns out I have to create a same temporary column, copy all data to that new column, remove the original column, and rename the temporary column back to that original name. Another way is to create a temporary table without setting "Is Identity" and copy all data ... Oh my, such a “simple” work!

But fortunately there is a tip available to generate SQL script for database schema change: In SQL Server Enterprise Manager, when you change table schema, you can let Enterprise Manager generate the schema change script for you. The most left button of this tool bar is used to “Generate change script”:


Yesterday, I used it to generate a complex script to remove “Identity” setting for a column:

-- To remove Identity setting of the Id column
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.partner_attribute_name
  DROP CONSTRAINT DF_partner_attribute_name_last_update_date
GO
ALTER TABLE dbo.partner_attribute_name
  DROP CONSTRAINT DF_partner_attribute_name_creation_date
GO
-- Create a temp table
CREATE TABLE dbo.Tmp_partner_attribute_name
 (
 partner_attribute_name_id int NOT NULL,
 attribute_name varchar(50) NOT NULL,
 description varchar(200) NOT NULL,
 last_update_date datetime NOT NULL,
 creation_date datetime NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_partner_attribute_name ADD CONSTRAINT
  DF_partner_attribute_name_last_update_date 
  DEFAULT (getdate()) FOR last_update_date
GO
ALTER TABLE dbo.Tmp_partner_attribute_name ADD CONSTRAINT
  DF_partner_attribute_name_creation_date 
  DEFAULT (getdate()) FOR creation_date
GO
-- Copy all data to the temp table
IF EXISTS(SELECT * FROM dbo.partner_attribute_name)
  EXEC('INSERT INTO dbo.Tmp_partner_attribute_name
   (partner_attribute_name_id, attribute_name
    , description, last_update_date, creation_date)
    SELECT partner_attribute_name_id
     , attribute_name, description
     , last_update_date, creation_date 
    FROM dbo.partner_attribute_name 
    WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.partner_attribute_name
GO
EXECUTE sp_rename N'dbo.Tmp_partner_attribute_name'
    , N'partner_attribute_name', 'OBJECT' 
GO
ALTER TABLE dbo.partner_attribute_name ADD CONSTRAINT
    PK_partner_attribute_name PRIMARY KEY CLUSTERED 
    (
        partner_attribute_name_id
    ) ON [PRIMARY]
GO
COMMIT

Note: You should generate the script BEFORE you save the changes in EM. Otherwise, that button will be disabled.

A small compiler - Wilco Syntax Highlighting at 11:49 AM

Although I am quite busy these days for my projects at hand, I still try to find time to do what I really want: to dig into compiler, operating system, and CLR framework.

Yesterday, I spent hours to analyze a popular syntax highlighter tool - Wilco SyntaxHighter, because it is a small compiler to some degree. :)

The highlighter parses string source (the code to be highlighted) to scan tokens (comment, string, key word). Each token includes position/length information in the string source and related with highlighter style data. Then the parser reads the source again to merge those parsed tokens. The string segment of the token will be updated with style data. Other string segment will leave as-is.

The good feature of that parser is to build a scanner chain. For example, to parse C# code, these scanner will be used: CommentBlockScanner (/* ... */) -- CommentLineScanner (//) -- StringBlockScanner (@) -- StringLineScanner ("") -- WordScanner. When the current and following characters match CommentBlockScanner, the CommentBlockScanner will continue to read characters to the end of the comment block and take that block as a Comment token; if the current character does not match CommentBlockScanner, then it may match the next scanner in the scanner chain ... If the character does not match any scanner, then it does not belong to a token and should be ignored.

For different type of language (e.g. Java, CSS, etc), we can build and use different scanner chain. But the basic parsing logic is still same.

The concept of Compiler is very useful when generating code dynamically. When the theory of "Software Factory" becomes real, code generation tool will be the fundamental in the system.

MS AJAX UpdatePanel throws Sys.WebForms.PageRequestManagerParserErrorException at 11:02 AM

I wrote ASP.NET code to call a service to generate and download a PDF file. At first, I tried to use AJAX UpdatePanel in the ASP.NET code to show progress because the service call may take long time. When the service call returned, I wanted to show the PDF file directly in browser on the same ASP.NET page:

  // Stream PDF kit to client
  Byte[] buffer = File.ReadAllBytes(filePath);
  base.Response.Clear();
  base.Response.ContentType = "application/pdf";
  int length = buffer.Length;
  base.Response.AddHeader("Accept-Header", length.ToString());
  base.Response.AddHeader("Content-Length", length.ToString());
  base.Response.OutputStream.Write(buffer, 0, buffer.Length);
  base.Response.Flush();
  base.Response.End();

But I saw this exception message:

Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed ...

Then I realized the UpdatePanel JavaScript tried to parse the returned data for the original web page, but it received strange PDF stream instead. I found the explanation here:

The UpdatePanel control uses asynchronous postbacks to control which parts of the page get rendered. It does this using a whole bunch of JavaScript on the client and a whole bunch of C# on the server. Asynchronous postbacks are exactly the same as regular postbacks except for one important thing: the rendering. Asynchronous postbacks go through the same life cycles events as regular pages (this is a question I get asked often). Only at the render phase do things get different. We capture the rendering of only the UpdatePanels that we care about and send it down to the client using a special format. In addition, we send out some other pieces of information, such as the page title, hidden form values, the form action URL, and lists of scripts.

It turned out that I should using Response.Redirect() to another page to show the PDF file in browser. To avoid the same exception, there is no AJAX code in the second page.