Caution: Education Ahead!

Ok, so I admit that I'm a little behind the curve on some of the technology that is available right now but all that is about to change. For my birthday, my wife presented me with a new Seagate 80GB hard drive which I am proud to say, installed without any problem. As I was installing the drive, I even decided that with the CD burner and a 256MB flash drive, I no longer needed the floppy drive installed so I'm finally running without a 3.5" drive on all of my computers.

The reason I asked my wife for a new hard drive is so I could set up my system for dual boot with a developer license of Windows Server 2003 Enterprise Edition and load other software such as SQL Server 2005 Beta 1 and Visual Studio 2005 Beta 2 so I could get myself up to speed on the upcoming technology. I don't have everything installed yet since I still have to download the MSDN portion of Visual Studio but what I have installed so far impresses me beyond my wildest dreams.

Even though I have been reading some about SQL Server 2005 tonight was my first exposure to it and therefore, it was my experience with SQL Server Enterprise Studio. I must say that so far I like what MS has done. The whole Visual Studio-like interface seems to be extremely intuitive and easy to use from what I have looked at. I know that I have barely scratched the surface of the new tools and I am also left wondering how many DBAs will like the extreme change.

In all honesty, tonight was also my first exposure to Windows 2003 Server and I am also impressed with it. I know that the product has been available for some time but I have not had access to any resource to dig in and fiddle around. I am looking forward to playing around with IIS6 and many of the other enhancements over Windows 2000 though I'm really primarily interested in SQL Server 2005.

As I was opening the box in preparation to install the new drive, I asked my wife if she realized that the present meant I was going to disappear into my home office more than I usually do since she was opening the door for me to install a shload of new "toys," She said she did but we'll see! As I learn more about SQL Server 2005 and Visual Studio 2005 I'll be sure to post it here.

Recent Events

It has been a few days since my last post and a lot has been going on. Over the past week I have:

• Found a host and launched a web site for Springfield Publications


• Attended an MSDN event in Greenwood, Indiana


• Attended the year-end/holiday event for my organization


• Started my holiday shopping

The event most relevant to this blog is the MSDN event, of course! This event focused on object oriented (oo) concepts, the Microsoft MapPoint Web Services, optimizing ASP.NET applications, and ASP.NET 2.0 Membership and Personalization. Overall, I really didn't get much out of the event. To me, the most valuable session was the one about optimizing ASP.NET applications.

The OO session focused on the concepts as they related to VB.NET and were presented in a simplistic manner. In all honesty, I found it a little hard to believe that in a theatre full of .NET developers, there were plenty of people that were unfamiliar with the pillars of OO development. It also seemed as though much of the emphasis was placed on abstraction and encapsulation as opposed to inheritance and polymorphism, the main principles on which the .NET framework is based!

The MapPoint Web Services session seemed kind of pointless and seemed like even more of a sales pitch than anything else. Are there applications for the Web Services? Sure, but the many applications that need some mapping functinality will be better served by linking to MapQuest. The bottom line from this session is that MapPoint is expensive, has inflexible licensing, and, although powerful, is probably overkill for many applications.

As I mentioned earlier, the best session (and actually the reason I attended this event) was the session regarding ASP.NET application optimization. At first, I was disappointed with this session since much of the first 45 minutes focused on the tools and how to use them. I am a programmer and, naturally, a tinkerer. I want to figure out the tools on my own (and trust me, they're not that complicated to use), not have every feature demonstrated. Some nice tips such as running the tools on a separate box than where the site is hosted were covered but I was looking for tips on how to actually improve performance.

Fortunately some tips were eventually discussed. Unfortunately most of them were ones that could be found in any ASP.NET book. Optimizations discussed included:

• Using caching


• Using connection pooling


• Open late/close early technique for database connections

All of the suggestions were nice but again, most of them could be found in any ASP.NET book. As always, the thing to keep in mind with these is that they are only as useful as your application allows them to be. If your application uses frequently changing data for instance, caching won't be of much use to you for improving the performance of your application.

A positive note about this session is that the presenter (Jacob Cynamon) continually emphasized the importance of designing performance into the application from the start. Poor performance should not be acceptable for any application. Applications should be tweaked to ensure the most efficient execution path is executed rather than a method that is simply easier to maintain. I am not arguing that maintainabilityis not important but I am arguing that usability is and performance directly impacts usability. Making users wait on your application because of poorly written, inefficient code is unacceptable. Of course, the amount of acceptible wait time varies by task but making users wait longer than necessary is a sign of low quality.

Moving on from my side rant, I did not stay for the final session. I originally intended on sticking around but decided that I will look into ASP.NET 2.0 on my test workstation when I get around to setting it up rather than sit through a presentation on beta software. I'm sure that many of the new features in ASP.NET 2.0 will make my life easier but for now, I am not interested. When the next version is closer to release, I'll look into this in more detail.

That really seems to wrap it up for the overview of the MSDN Event. Until next time, happy coding!

Any Command Line Lovers Out There?

To be honest, much of the buzz about Longhorn hasn't really gotten me very excited. All that I've been really interested in is WinFS but I haven't spent much time looking into it yet. Tonight I ran across something called "Monad" which managed to get my attention.

Announced at the Professional Developer's Conference, Monad is a replacement for cmd.exe. Personally, I love using the command line. I've always found that most administrative tasks are much easier to perform using a command line interface than to dig through layer upon layer of windows and dialog boxes just to click one check box. Sure, some things are clumsy in the command line but at least its fast, there's no question about what is actually happening, and most important, there's NO DIGGING! I even extend this to database administration where I prefer to use Query Analyzer to Enterprise Manager for most SQL Server administrative tasks.

You can imagine my dismay when I read that my favorite tool in Windows administration was being replaced. "What could they possibly do to it?" I wondered.

Long story short, Microsoft has finally decided to catch up with the rest of the operating system world and switch to a pipeline based model. The nicest thing about Monad is that rather than being an ASCII based pipeline, its a .NET object based pipeline. What does all this mean? It means that administrative tasks are going to get a whole lot easier!

Perhaps one of the nicest examples I've seen regarding how Monad simplifies administrative tasks comes from an article on TechWorld. In the example, the task of enumerating processes to determine how much memory is being used by each process in Windows 2003 (using some third party tools) is compared to the same task on Monad. Basically, rather than writing a custom script in Python, a new "get-command" command has been added which returns a collection of System.Diagnostics.Process objects which can be piped to Excel, an HTML file, or a .NET Grid control. The results can even be piped to further commands.

Maybe Longhorn isn't going to be so bad afterall!

For more information about Monad, Google "Monad"

Stored Procedures in Access

A common subject on the various newsgroups is that of stored procedures in Access. Until tonight, I was under the impression that stored procedures could not be used in Access except through an Access Project or a pass-through query. I was right to an extent. Some Data Definition Language (DDL) commands (including CREATE PROCEDURE) are valid commands when executed against an Access database when connecting to the database via the Jet 4 OleDb provider.

Despite claims that stored procedures could be created and used directly by Access I had never seen a functional example of an Access stored procedure. To those that I have debated with regarding this, I concede that CREATE PROCEDURE works with Access; Access does indeed support an extremely limited type of stored procedures starting with Access 2000.

There is a great deal of documentation floating around the internet pertaining to this subject. Nearly all of the documentation states that Access does not support stored procedures. Of the documentation that claims that Access does support stored procedures, most of it provides no example of how to create and execute them.

When I saw yet another post on this subject tonight, I began typing the typical reply of "You can't do it, Access doesn't support it," but also continued looking for information to back up my claim. Everything I found in previous searches on this subject fit into the categories I described above. Tonight I stumbled across a fairly recent newsgroup discussion and a Microsoft Knowledge Base article both dealing with this subject directly.

The CREATE PROCEDURE command in Access cannot be used directly from the IDE. It must be executed over a separate connection using the Jet 4.0 OleDb provider.

The examples provided in the links above are specific to the VB Modules in Access. I decided to carry this a bit further and try this from within the .NET Framework. Below is a simple Console application that I wrote to test this. The code isn't anything complicated; in fact, it should be pretty simple for anyone familiar with the OleDb namespace classes.

using System;

using System.Data;

using System.Data.OleDb;

using System.Text.RegularExpressions;



namespace AccessSPTest

{

  public abstract class AccessTests

  {

    public static void Main()

    {

      using (OleDbConnection cn = new OleDbConnection(

        @"Provider=Microsoft.Jet.OLEDB.4.0;" +

         "Data Source=c:\inetpub\wwwroot\recipemanager\recipe.mdb"))

      {

        byte option = GetOption();

        while(option!=7)

        {

          switch(option)

          {

            case 1:

              CreateSelectProcedure(cn);

              break;

            case 2:

              DropSelectProcedure(cn);

              break;

            case 3:

              ExecuteSelectProcedure(cn);

              break;

            case 4:

              CreateInsertProcedure(cn);

              break;

            case 5:

              DropInsertProcedure(cn);

              break;

            case 6:

              ExecuteInsertProcedure(cn);

              break;

            case 7:

              break;

            default:

              Console.WriteLine("Invalid Option.\n");

              break;

          }



          option = GetOption();

        }

      }

    }



    private static byte GetOption()

    {

      Regex re = new Regex(@"^\d{1}$");

      byte option = 0;

      while(option==0)

      {

        Console.WriteLine("Select an option:");

        Console.WriteLine("\t1.) Create Select Procedure");

        Console.WriteLine("\t2.) Drop Select Procedure");

        Console.WriteLine("\t3.) Execute Select Procedure");

        Console.WriteLine("\t4.) Create Insert Procedure");

        Console.WriteLine("\t5.) Drop Select Procedure");

        Console.WriteLine("\t6.) Execute Insert Procedure");

        Console.WriteLine("\t7.) Exit");

        Console.Write("Enter a number (1-7): ");

        string input = Console.ReadLine();



        if(re.IsMatch(input))

          option = Convert.ToByte(input);

      }

      return option;

    }



    private static void DropSelectProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdDrop = new OleDbCommand(

          "DROP PROCEDURE sp_Ingredient2",

          conn);

        cmdDrop.CommandType = CommandType.Text;

        cmdDrop.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be dropped.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void CreateSelectProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdCreate = new OleDbCommand(

          "CREATE PROCEDURE sp_Ingredient2(prmIngredientID int)" +

          " AS SELECT * FROM Ingredient WHERE IngredientID = prmIngredientID",

          conn);

        cmdCreate.CommandType = CommandType.Text;

        cmdCreate.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be created.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void ExecuteSelectProcedure(OleDbConnection conn)

    {

      int id = 0;

      Regex re = new Regex(@"^\d+$");

      do

      {

        Console.Write("Ingredient ID: ");

        string input = Console.ReadLine();

        if(re.IsMatch(input))

          id = Convert.ToInt32(input);

      } while(id == 0);

			

      OleDbCommand cmd = new OleDbCommand("sp_Ingredient2", conn);

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add(new OleDbParameter("prmIngredientID", OleDbType.Integer));

      cmd.Parameters["prmIngredientID"].Value = id;

			

      conn.Open();

      try

      {

        using(OleDbDataReader reader = cmd.ExecuteReader())

        {

          while(reader.Read())

          {

            // Write the ID and Ingredient Name

            Console.WriteLine("{0}\t{1}", reader.GetInt32(0), reader.GetString(1));

          }

        }

      }

      catch(Exception ex)

      {

        Console.WriteLine("Could not execute select procedure.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void DropInsertProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdDrop = new OleDbCommand(

          "DROP PROCEDURE sp_InsertIngredient", conn);

        cmdDrop.CommandType = CommandType.Text;

        cmdDrop.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be dropped.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void CreateInsertProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdCreate = new OleDbCommand(

          "CREATE PROCEDURE sp_InsertIngredient(prmIngredientName varchar(25))" +

          " AS INSERT INTO Ingredient (Description) VALUES (prmIngredientName)"

          , conn);

        cmdCreate.CommandType = CommandType.Text;

        cmdCreate.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be created.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void ExecuteInsertProcedure(OleDbConnection conn)

    {

      string value = String.Empty;

      do

      {

        Console.Write("Value to insert: ");

        value = Console.ReadLine();

      } while(value == String.Empty);

		

      OleDbCommand cmd = new OleDbCommand("sp_InsertIngredient", conn);

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add(new OleDbParameter("prmIngredientName",

        OleDbType.VarChar, 25));

      cmd.Parameters["prmIngredientName"].Value = value;

       conn.Open();

      try

      {

        using(OleDbDataReader reader = cmd.ExecuteReader())

        {

          while(reader.Read())

          {

            // Write the ID and Ingredient Name

            Console.WriteLine("{0}\t{1}",

              reader.GetInt32(0), reader.GetString(1));

          }

        }

      }

      catch(Exception ex)

      {

        Console.WriteLine("Could not execute insert procedure.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }

  }

}

Note that this code does not check if an object exists before the drop statement is called. Access keeps track of objects in a system table called MSysObjects. Checking if an object exists requires some up front configuration.

The first step towards verifying the presense of an object is to make sure that system tables are visible in Access. If they are not visible, go to Tools / Options... and check the box for system tables in the show group on the View tab. Apply the change.

The second step is to ensure that the user account has read access to the design and data of the MSysObjects table. By default this access has not been granted. To enable the access, go to Tools / Security / User and Group Permissions... Select the appropriate user account and ensure that "Object Type" is set to "Table" then click the MSysObjects table from the "Object Name" list. Click "Read Data," "Read Design" should be checked automatically. Apply the change.

At this point, the permissions to access MSysObjects should be set properly. To check if an object exists, execute the following command:

SELECT COUNT(*) FROM MSysObjects WHERE Name=?

Executing the above statement through an OleDbCommand using ExecuteScalar() will return an integer of 0 or 1. If the value is 1, the object exists.

OleDbCommand cmd = new OleDbCommand(

  "SELECT COUNT(*) FROM MSysObjects WHERE Name=?", conn);

cmd.Parameters.Add(new OleDbParameter("param1", OleDbType.VarChar, 255));

cmd.Parameters["param1"].Value = object_name;

conn.Open();

int matches = (int)cmd.ExecuteScalar();

if(matches==1)

  Console.WriteLine("Object Found.");

else

  Console.WriteLine("Object Not Found.");

conn.Close();

The DDL seems to follow the SQL standards pretty well in terms of syntax but that is where the commonality stops. Recall that I said that these stored procedures are extremely limited. To see how limited these so called "stored procedures" truly are, a look under the hood is necessary.

Like most modern database management systems, Access database objects are divided into a number of categories including Tables, Users, etc... For the purposes of this discussion, I will limit the focus to Access' Query type.

Whenever an object is created in Access, a row is added to the MSysObjects system table. The MSysObjects table (which we made visible earlier) maintains configuration and reference data about each object including its type. A value of "5" in the Type column indicates that the object represented by the given row, is a stored query.

Any object created with the CREATE PROCEDURE command has a type of 5. This tells us that a procedure was not created but a QueryDef was created instead. In fact, the online Office reference makes no attempt to hide that fact as is indicated in the following excerpt. I've provided the link for reference.

procedure
Similar to a view except that the SQL syntax can allow for SELECT statements (row returning queries) to pass parameters and allow for DML (non-row returning queries) UPDATE, INSERT, DELETE, and SELECT INTO. Literally the same as a querydef, except that it can be created from SQL syntax without the use of the DAO object model.

In Access 10 (I haven't checked later versions), these objects are listed in the Query list and are displayed with the corresponding "Action Query" icon. We can also look at the SQL definition of one of the objects for additional confirmation.

PARAMETERS prmIngredientName Text ( 255 );

INSERT INTO Ingredient ( Description )

SELECT prmIngredientName AS Expr1;

What does all of this looking under the hood mean? It means that the objects that are created with the CREATE PROCEDURE command are not stored procedures as enterprise developers know them. They provide no support for any type of flow control statements or other logic; they are simply single statement queries that are capable of accepting parameters. This is nothing more than a roundabout way to create objects that are accessible from the Query menu.

This being said, it would be nice to be able to execute a statement such as:

IF EXISTS(SELECT * FROM MSysObjects WHERE Name="objName") DROP PROCEDURE objName

but statements like that just won't work due to the limited functionality of Access' SQL processor.

More often than not, Access is not the proper tool for the job. Don't get met wrong, Access is great for small, workgroup based solutions. Solutions requiring more power (such as SQL Server or Oracle) are needed when the problem being addressed extends much further than that. Misleading statements such as "Access supports stored procedures" only serve to compound scalability and performance issues when inexperienced developers are faced with the decision of which DBMS to use.