Jerry Dixon

My Blog has Moved

I've made the switch to a new blog host. You can find me now at jdixon.dotnetdevelopersjournal.com.

I would like to thank Donny and the rest of the team for providing a great hosting site for me over the past year.

It's been great.

Is Comment Moderation the Answer?

You know how funny life is sometimes? Your program works great until you tell someone that it works great, then it crashes hard. Or, in my case, I turned off blog comments because the spam was driving me nuts and no one has been leaving real comments anyway. The very next day, I started receiving some good comments on a couple of my posts. <Sigh> So, I'm turning the comments back on, and will just have to zap the spam as it comes in.

On a better note, I noticed that the .Text engine may have a solution. Apparently they have a version that features comment moderation, along with one of those Security Code features with text inside a bitmap. This might annoy some users, though. A couple of blogs (http://www.peterprovost.org/archive/2004/08/13/1763.aspx, http://scottwater.com/blog/archive/2004/08/08/12888) mention negative feedback.

So what do you think? Is comment moderation the answer, or should we put up with having to delete comment spam multiple times a day?

Comment Spam Stinks!

Well, it finally happened. Because of the enormous amount of comment spam that I have been receiving, I have joined the ranks of those that have disabled blog comments. If someone wants to comment on one of my posts, please send the comment to me via the "Contact" option under "My Links."

I am very disappointed that this has become necessary, but I have been deleting spam from my blog three or four times a day. Spammers won't be content until they have ruined blogs, just like they have ruined email.

Critical Sections in SQL Server

Many of us more experienced (NOT older) techies like to speak of learning things the hard way. This definitely applies to me and the way I learned about SQL Server 2000. I've had to learn it all on the job, on an as-needed basis. This means that there are gaps in my knowledge. Every once in a while, I get to learn something about SQL Server that I never knew before. This time, it was the fact that SQL Server supports critical sections.

For those of you that may not know this term, a critical section is a piece of code that can be safely run by only one process at a time. In a multi-threaded application, this applies to each individual thread; only one thread can safely execute a piece of code at a time. The term “Critical Section“ is common in languages with roots in C or C++.

I found myself with just this situation with a stored procedure. I found that there are two system stored procedures, sp_getapplock and sp_releaseapplock, that implement this concept. Here is an excerpt from SQL Books Online:

sp_getapplock

Places a lock on an application resource.

Syntax

sp_getapplock [ @Resource = ] 'resource_name',
    [ @LockMode = ] 'lock_mode'
    [ , [ @LockOwner = ] 'lock_owner' ]
    [ , [ @LockTimeout = ] 'value' ]

Arguments

[@Resource =] 'resource_name'

Is a lock resource name specified by the client application. The application must ensure the resource is unique. The specified name is hashed internally into a value that can be stored in the SQL Server lock manager. resource name is nvarchar(255), with no default.

[@LockMode =] 'lock_mode'

Is a lock mode. lock_mode is nvarchar(32), with no default, and can be one of these values: Shared, Update, Exclusive, IntentExclusive, IntentShared.

[@LockOwner =] 'lock_owner'

Is the lock owner. lock_owner is nvarchar(32) and can be Transaction (the default), or Session. When the lock_owner value is the default, or when Transaction is specified explicitly, sp_getapplock must be executed from within a transaction.

[@LockTimeout =] 'value'

Is a lock time-out value, in milliseconds. The default value is the same as the value returned by @@LOCK_TIMEOUT. To indicate that lock requests that cannot be granted immediately should return an error rather than wait for the lock, specify 0.

Return Code Values

>= 0 (success) or < 0 (failure)

Value	Result
0	Lock was successfully granted synchronously.
1	Lock was granted successfully after waiting for other incompatible locks to be released.
-1	Lock request timed out.
-2	Lock request was cancelled.
-3	Lock request was chosen as a deadlock victim.
-999	Parameter validation or other call error.

Cool. This is exactly the kind of “semaphore-lock“ that I cut my teeth on. Of course, it turns out that I didn't really need this. I discovered a better way to implement my task. However, I thought that I would pass this along, since I had never heard of it before. Maybe it will help someone else.

Limitation in SQL's XML Support

I managed to outsmart myself again....

I've been working on an application that processes large XML files. One of the routines involves passing the XML to a stored procedure, which then inserts certain parts of the XML into various tables. This is done via sp_xml_preparedocument and openxml. This is fairly trivial and works quite well.

Now, this system processes a lot of XML. Some of the tables involved have reached 15+ million rows, and insert performance has decreased to a point where I had to take action. I decided that, in addition to normal SQL tuning, I would break this task into two parts. First, the XML would be placed, unparsed, into a staging table. A separate process would take the data from this table, perform the proper parsing via openxml, and insert the resulting data into the various destination tables. This separation of work would allow the main process to operate more quickly.

I created the staging table and tested the system performance. The system ran 400% faster. Cool.

Next, I had to write the process that would retrieve the data from this staging table and insert it into the destination tables. This is when I discovered my mistake.

The XML is stored in NTEXT fields in the staging table. I needed to read this into a local variable, so that I could pass it to the sp_xml_preparedocument stored procedure. This can't be done, because local variables can't be declared as NTEXT. I was stuck.

I found a good article on the net that explained a workaround. However, in the article, the author states that he has used this workaround on documents that were “over 36,000 bytes in size.” My documents sometimes reach 20 MB in size. His method doesn't work on documents over about 300K. (You should check it out, though. It's a good read.) I had to solve my own problem.

So, as I was running out of time, I decided to fall back to the one solution that I knew would work. I wrote a .NET process that runs on a background thread. This process reads the XML from the staging table (via an FOR XML EXPLICIT query), parses this data via a couple of calls to the XmlDocument's SelectSingleNode method, and then uses another stored procedure to insert the data into the destination tables. This works, but I don't like it. I would rather have all of this data manipulation occur on the SQL Server, instead of being passed back and forth between the SQL Server and the Application Server. If anyone wants to send some alternatives my way, I would be most appreciative.

I can't wait for SQL 2005. Maybe this kind of thing will go away....

Using WMI in .NET

I recently needed to write some code to determine the percentage of free disk space on a drive. I was a bit surprised when I found that this information wasn't immediately available in some .NET class. However, I did stumble upon the System.Management namespace. This namespace contains classes that allow a .NET programmer to query any of the vast array of Windows Management Instrumentation (WMI) classes. These classes allow a program to obtain all kinds of data about a system, including free and total disk space.

Here is some code that uses the ManagementObject class:

// get parms from the command line
DiskDrive = args[4].Replace(":","").ToUpper();

// run the query
mo = new ManagementObject(string.Format("Win32_LogicalDisk.DeviceID=\"{0}:\"", DiskDrive));

// store results
FreeSpace = double.Parse(mo["FreeSpace"].ToString());
TotalSpace = double.Parse(mo["Size"].ToString());

CurrentValue = (int)Math.Round((FreeSpace / TotalSpace) * 100, 0);

Now, the example above does a direct query against a single instance of a WMI class. Sometimes this isn't possible. Some WMI classes return multiple instances, which need to be iterated. Here is an example that returns the status of a Windows Service:

// get parms from the command line
Service = args[2].ToString().ToUpper();

// run the query
Query = string.Format("select State,Name from Win32_BaseService where Name=\"{0}\"", Service);

mos = new ManagementObjectSearcher(Query);

// find the WMI instance for the indicated service
foreach (ManagementObject mo in mos.Get())
{
Name = mo["Name"].ToString().ToUpper();

          if (Name == Service)
          {
               State = mo["State"].ToString();
          }
}

See here and here for two of the MSDN pages that I used most.

SQL Server 2005 WebCasts

Microsoft will begin running SQL Server 2005 webcasts on December 6^th. Initially, there will be 15 webcasts that introduce the new features of SQL 2005. Starting in January, they will be showing at least 1 webcast per week, focusing on specific developer topics.

Everyone who attends a webcast will receive a copy of the Beta 2 Resource Kit. They will also receive a copy of the Beta 3 Resource kit when it ships. In addition, the first 1,500 people to watch 5 or more webcasts will receive a special SQL 2005 t-shirt. All webcast viewers will be entered in a drawing to win an XBox. (One XBox for each day of the week.)

Full rules can be found at http://msdn.microsoft.com/SQL/WebcastRules.aspx.
The webcasts can be viewed at http://msdn.microsoft.com/SQL/2005Webcasts.