Checking object existence in SQL 2000 and SQL 2005

I have SQL 2005 on my machine but sometimes I am connecting to SQL 2000 database. If you generate drop object script from SQL 2005 Server Management Studio, it cannot be applied to the SQL 2000 database because they do not have the same system tables and views. For example dropping a stored procedure:

SQL 2005 syntax:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[StoredProc_Name]

However SQL 2000 database does not have sys.objects table. In SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StoredProc_Name]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[StoredProc_Name]

This will work in SQL 2005 database as well because SQL 2005 database has a view named sysobjects (for backward compatibility?!).

To check function existence in SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Function_Name]') AND xtype in (N'FN', N'IF', N'TF')) DROP FUNCTION [dbo].[Function_Name]

To check table existence in SQL 2000 syntax:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Table_Name]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[TableName]

Absolute positioned div and dropdown box

An old bug has been fixed in IE 7. It's always working as expected in FireFox but as I can remember it wasn't drawing it out nicely (it is now, in 2.0.0.6).

See the code below:

<div id="DivA" style="position:absolute; width:200px; height:200px; background-color:Green; display:block; z-index:100;">I am a div</div> <select name="DropDownList1" id="DropDownList1"> <option value="Item1">Item1</option> <option value="Item2">Item2</option> <option value="Item3">Item3</option> <option value="Item4">Item4</option> </select>

In IE 6 showing a div in absolute position on top of / overlapping a select (drop down box) would always fail. The select box will ALWAYS be on top of everything except one - the iFrame. Therefore we need an iframe with exactly the same size draw beneath the div in order to hide the select box completely. In IE7 this trick is no longer required. :)

T-SQL Cursor syntax

I've been asked a few times about the cursor syntax (and actually myself have been forgetting too). I know the SQL books online has syntax for everything, but apparently it is a bit hard to read some times. Anyway, post this here so it's handy for myself and the others. :)

DECLARE some_cur CURSOR FOR SELECT Column1, Column2, Column3 FROM TableName OPEN some_cur DECLARE @col1 INT, @col2 NVARCHAR(50), @col3 BIT FETCH NEXT FROM some_cur INTO @col1, @col2, @col3 WHILE (@@fetch_status <> -1) BEGIN -- Do whatever you want FETCH NEXT FROM some_cur INTO @col1, @col2, @col3 END CLOSE some_cur DEALLOCATE some_cur

Hide Blogger's navbar

I am not sure would Blogger tell me to delete this post, but my other blogs have been hiding the navbar all the time. I thought of recreate my navbar and place it somewhere inside the blog instead of the top, but give up after I've found out it cannot search Chinese characters effectively (my other blogs are in Chinese, obviously). Anyway, here is the piece to hide the navbar:

<script type="text/javascript"&gt; var navBar = document.getElementById("navbar"); if (navBar) navBar.style.display = "none"; </script>

Simple Hover Image/Button

Back in the old days, when I have completely no idea about HTML, I found that the HoverButton created by Microsoft FrontPage was very exciting. Now hover button is nothing fancy. It's simply using onmouseover and onmouseout events.

<input type="image" name="HoverButton1" id="HoverButton1" onmouseover="this.src='/grey_btn.gif';" onmouseout="this.src='/green_btn.gif';" src="/green_btn.gif" />

Personally I prefer wrap these up in a web control (extending ImageButton).
Download ExtendedWebControls.dll & HoverButton.cs

So in development I can add it to my toolbox and use it as a normal web control.

<%@ Register Assembly="ExtendedWebControls" Namespace="ExtendedWebControls" TagPrefix="cc1" %> ... <cc1:HoverButton ID="HoverButton1" runat="server" BaseImgURL="green_btn.gif" HoverImgURL="grey_btn.gif" />

ReadOnly Textbox ViewState in .NET 2.0

In .NET 2.0, behaviour of TextBox has slightly changed. For a TextBox with ReadOnly property set to true then the text is not be posted back to the server side. To set a TextBox read-only and keep the text on post back, use the following to set the readonly property on client site rendering:

TextBoxA.Attributes.Add("readonly", "readonly")

Update/Delete .NET 2.0 web application sub directories

Joseph and I have spent a long time figuring out why the session variable is refreshed after a page load. It was working well in .NET 1.0, but it fails when after we've converted the project to .NET 2.0. At first we have no idea it is a .NET 2.0 issue. Actually we didn't even know it is a session variable issue (all we got was the mysterious object null reference exception). Anyway, after some difficult time we found out updating sub directories restart the web application. Unfortunately we have to keep this operation. After some googling we finally found a solution, and here it is.

If the sub directory is linked to another directory (as conjunction), then updating that sub directory would not trigger application restarts. For example my application is in C:\MyApp and the sub directory is C:\MyApp\SubDir. I should create another directory, for example C:\MyAppSubDirLink. Then download Window Server 2003 Resource Kit (here) and install it.

The resource kit has "linkd" command, which is all we need to get this working. Run the following in command prompt:

linkd C:\MyApp\SubDir C:\MyAppSubDirLink

That's it! Now you can update C:\MyApp\SubDir without restarting the web application, and therefore session variable would be preserved!

Pass out BIG Number from Flash

Working with big number just inside flash is all sweet (Number.MAX_VALUE approx. 1.7976931348623158e+308) , but when you need to pass it out by xml it would be a pain because it would become a 14 decimal places scientific notation somewhere between 2^49 and 2^50. (which is the same behaviour as most of the languages).

2^49 = 562949953421312
2^50 = 1125899906842624 which to string would be 1.12589990684262e+15 (note that a "4" is missing at the end)

So passing out big number as string and convert it back to a number from it would not work (obviously)!

We were using bit mask for marking selections, i.e. 101001 indicates 0,3 and 5 are selected. I didn't notice this issue until we have 50 selections and people are choosing multiple (i.e. 50 and something else, which means 2^49 plus some more). I have been finding a easy solution but apparently there is none. Therefore I've decided to do the following in ActionScript:

static function GenerateBitMask(listOfPowers:Array):Array{ var bitmask:Number = 0; var bitmaskHead:Number = 0x0; var bitmaskTail:Number = 0x0; // Loop through all selection for (var index = 0; index<listOfPowers.length; index++) { var indHead:Number = 0; var indTail:Number = 0; // if power > 32, set in bitmaskHea if (listOfPowers[index] > 32){ indHead = listOfPowers[index] - 32; bitmaskHead = bitmaskHead + (Math.pow(2, indHead -1)); } // if power <= 32, set in bitmaskTai else{ indTail = listOfPowers[index]; bitmaskTail = bitmaskTail + (Math.pow(2, indTail -1)); } } // Combine bitmaskHead and bitmaskTail to get bitmask for flash usag bitmask = bitmaskHead * (Math.pow(2, 32)) + bitmaskTail; var bitmaskArray:Array = new Array(); bitmaskArray[0]=bitmask; bitmaskArray[1]=bitmaskHead; bitmaskArray[2]=bitmaskTail; return bitmaskArray; }

I do have a reason for picking 32 as the magic number, but it doesn't really matter. The bitmaskTail and bitmaskHead are passed to the web application (which in this case .NET C#) and the web application need to reconstruct the number:

XmlNode bitmaskHead = selectionNode.Attributes["BitmaskHead"]; XmlNode bitmaskTail = selectionNode.Attributes["BitmaskTail"]; ulong bigNumber = Convert.ToUInt64(ulong.Parse(bitmaskHead.InnerText) * Math.Pow(2, 32) + ulong.Parse(bitmaskTail.InnerText));

To pass big number to flash is basically the reverse process. Anyway I hope there is an easier way so if anyone have something in mind please tell me. Otherwise let's live with this pain. :D

XMLHTTP readystate

Previous post has mentioned about XMLHTTP. onreadystatechange event fire up at several stages which allow response handling. Generally readystate has the following values:

0	Uninitialised	before open() is called
1	Request has set up	before send() is called
2	Request was sent and in process	content header is available
3	In process	responseText holds partial data but unfinished
4	Complete	finish processing and received response

P.S. according to QuirksBlog: XMLHTTP notes: readyState and the events, different browsers behave differently for onreadystatechange event handling.

Javascript AJAX - XML DOM HTTP Request

.Net AJAX extension is a easy and good tool, however there are two projects I have worked on cannot use .Net AJAX to accomplish the job (well, one was done before the AJAX 1.0 extension released). First project was in old fashion ASP. The second one is in .NET 2.0, but it has the application hold on an intra network machine, which for security reason has no internet access. However it requires a call to a web services on the client side.

Therefore javascript AJAX comes in by using XML DOM HTTP Request technique. Note that for javascript wa of AJAX, same as .NET AJAX 1.0, cannot call cross domain web services easily (.Net AJAX extension cannot call it at all without writing a proxies!). In Internet explorer, adding the external web service to the trusted site list will allow cross domain access.

Step 1: define XMLHTTP object variable

var xmlHttp;

Step 2: Create your action - in this example I will retrieve a list of book titles from the BibleWebservice provided by http://www.webservicex.net/.

function Button1_onclick() { //Create SOAP Envelope var xmlObj = new ActiveXObject("Msxml2.DOMDocument") ; var sXml = "<?xml version=\"1.0\" ?>" ; sXml += "<soap:Envelope " sXml += "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"" ; sXml += "xmlns:xsd=\http://www.w3.org/2001/XMLSchema/" ; sXml += "xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">" ; sXml += "<soap:Body>" ; sXml += "<GetBookTitles xmlns=\"http://www.webserviceX.NET/\">"; sXml += "</GetBookTitles></soap:Body></soap:Envelope>" ; xmlObj.loadXML(sXml) ; xmlHttp = new ActiveXObject("Msxml2.XMLHTTP"); // Add Post, SOAP Action and Content-Type to the request header xmlHttp.Open ("Post", "http://www.webservicex.net/BibleWebservice.asmx", false) ; xmlHttp.setRequestHeader("SOAPAction", "http://www.webserviceX.NET/GetBookTitles") ; xmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8" ) ; // When response received call OnRetrieveComplete method xmlHttp.onreadystatechange = OnRetrieveComplete; // Send request xmlHttp.Send(xmlObj.xml) ; }

Step 3: Create response handler

function OnRetrieveComplete(){ // readystate4 = Completed if (xmlHttp.readyState == 4 && xmlHttp.status == 200){ // Get response and perform your action var xmlResponse = xmlHttp.responseXML; var bookXML = xmlResponse.selectSingleNode("soap:Envelope/soap:Body/ GetBookTitlesResponse/GetBookTitlesResult").text; var bookXMLObj = new ActiveXObject("Msxml2.DOMDocument") ; bookXMLObj.loadXML(bookXML); var bookNodes = bookXMLObj.selectNodes("NewDataSet/Table"); for (i=0; i<bookNodes.length; i++) { ... do whatever you want } } }

Simple anonymous delegate II - Predicate

Follow from the previous post, anonymous delegate can be used as predicate, which is a method that defines a set of criteria and determines whether the specified object meets those criteria. For example we have the following method:

public static void DoSomething<TObj>(IEnumerable<TObj> objs, Action<TObj> action, Predicate<TObj> pred) { foreach (TObj obj in objs) if(pred(obj)) action(obj); }

which takes a predicate parameter and only perform the action if the predicates is true.

We can pass in anonymous delegate for the predicate:

List<string> listOfStrings = new List<string>(); listOfStrings.Add("one"); listOfStrings.Add("two"); listOfStrings.Add("three"); AnonymousDelegate.DoSomething<string>(listOfStrings, delegate(string s) { Response.Write(s + "<br/>");}, delegate(string s) { return !s.StartsWith("o"); });

In this case on "two" and "three" would be written out.

Simple anonymous delegate

Although anonymous method has been supported by Java for a long long time, finally it comes to .NET 2.0. Here is a simple generic method:

public static void DoSomething<TObj>(IEnumerable<TObj> objs, Action<TObj> action){ foreach (TObj obj in objs) action(obj); }

We can pass in anonymous delegate for the action. The action represents a method that performs an action on the specified object. For example:

List<string> listOfStrings = new List<string>(); listOfStrings.Add("one"); listOfStrings.Add("two"); listOfStrings.Add("three"); AnonymousDelegate.DoSomething<string>(listOfStrings, delegate(string s) { Response.Write(s + "<br/>"); });

The output in this example would be:

one two three

.Net 2.0 configuration reload

In .Net 1.0, if you have changed the app.config settings of a service, a restart is require to pick up the setting. Web applications automatically restart when web.config is modified.

.Net 2.0 allows you to refresh cached application configuration setting with the following method:

ConfigurationManager.RefreshSection("appSettings");

which refreshes the named section so the next time it is retrieved it will be re-read from disk.

Check/find default

If you know the name of the default and you always have good naming convention that include table and column name in your default name, then you can simple check against the sysobjects table to find out if a default exists:

SELECT * FROM SysObjects O WHERE name = 'DF_DEFAULTNAME' AND xtype = 'D'

If you want to know a particular column has a default defined or not:

SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID INNER JOIN SysConstraints T ON C.CDEFAULT = T.CONSTID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName'

If the name of the default is what you are after:

SELECT O2.Name FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID INNER JOIN SysConstraints T ON C.CDEFAULT = T.CONSTID INNER JOIN SysObjects O2 ON T.CONSTID = O2.ID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName'

Check exists column

You may want to check if a column exists before adding the column or performing some action on that column. Here is the clause to check column existence.

IF EXISTS (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID WHERE ObjectProperty(O.ID,'IsUserTable')=1 AND O.Name='TableName' AND C.Name='ColumnName')

General alter table syntax

To add a primary key:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName)

For composite key, simply specify mutiple columns:

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY (ColumnName1, ColumnName2)

To add a foreign key:

ALTER TABLE ChildTableName ADD CONSTRAINT FK_NAME FOREIGN KEY (ChildTableColumnName) REFERENCES ParentTable(ParentTableColumnName)

To add a unique key:

ALTER TABLE TableName ADD CONSTRAINT UNIQUE_NAME UNIQUE (ColumnName)

To add a column:

ALTER TABLE TableName ADD ColumnName int NULL ALTER TABLE TableName ADD ColumnName int NOT NULL DEFAULT(0)

Note thate when you are adding a NOT NULL column which does not have a default value the statement would fail. If it has to be a NOT NULL column, you should add it as a NULL column first, update the table and set values of that column, then change it to NOT NULL with alter table statement. For example,

ALTER TABLE TableName ADD ColumnName int NULL UPDATE TableName SET ColumnName = ColumnName2 + 1 ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL

Add default value to existing column

I have spent quite a bit of time trying to figure out how to add a default constraint to an existing column in SQL Server (in T-SQL). I found the following from the SQL Server 2005 Books Online:

ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum Corporation'

Appoint checking the syntax, SQL Server 2005 gives me the following error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'set'.

I was so frustrated and have no idea why would this happened (until now I have completely no idea so please tell me if you know). Turn out I have written the following instead and it's working like a charm!

ALTER TABLE TableName WITH NOCHECK ADD CONSTRAINT DF_DefaultName DEFAULT 'Default Value' FOR ColumnName