• Out of Memory Exception While Attempting to Do SQL CLR

    Update: We figured out how to make it work with the help of our DBA and Jonathan Kehayias (see comments). We increased SQL Server’s MEM_TO_LEAVE property, by adjusting the –g command line switch for the service, to 448MB. This increase of the shared memory pool gave SQL Server enough breathing room for its worker threads, and now we are able to return 1536 records in 1 minute 21 seconds, including the Bitmap conversion.

    Furthermore, we found a workaround using our reporting engine, which we will implement when we have some breathing room ourselves in our rollout timeline. This will “get it working” for now, which is the requirement handed down from above. Soon though, we will have this working in our application tier, where it belongs.

    SQL Server’s CLR abilities are really cool. I have done some benchmarking, performing the same computations in both T-SQL and CLR and I have found CLR to outperform T-SQL by factors of greater than 10 to 1. It’s fantastic for this use.

    Unfortunately, it has won a battle against me today. I’ll provide a bit of background first. I am currently working on the Point of Sale component of my company’s pharmacy system, and in particular, I am writing all of the code associated with interacting with the signature pad. I’ve abstracted everything nicely, such that we can support multiple pad’s, and I just have to write a .Net class that implements the ISignaturePad interface.

    Obviously, other than the navigational aspects of buttons and listboxes and sale line items being displayed on the pad, capturing signatures themselves is paramount. Because each signature pad can spew out the signature data in a different way, and we want to store the “perfect” vector information, I’ve abstracted the signature data into two classes. The Signature and SignaturePoint classes’ definitions look like this:

        public partial class Signature
            public virtual SignaturePoint[] Points { get; protected set; }
            public virtual SignaturePoint TopLeft { get; set; }
            public virtual SignaturePoint BottomRight { get; set; }
            public int Width
                    return BottomRight.X - TopLeft.X;
            public int Height
                    return BottomRight.Y - TopLeft.Y;
            public int XDpi { get; set; }
            public int YDpi { get; set; }
            private Signature()
            public static Signature CreateFromTT8500String(string signaturePoints)
                var sig = new Signature();
                sig.Points = sig.CreateFromTT8500PointsData(signaturePoints);
                return sig;
            public virtual byte[] Serialize()
                using (var ms = new MemoryStream())
                    var bf = new BinaryFormatter();
                    bf.Serialize(ms, this);
                    return ms.ToArray();
            public static Signature Deserialize(byte[] serializedSignatureBytes)
                using (var ms = new MemoryStream(serializedSignatureBytes))
                    var bf = new BinaryFormatter();
                    return (Signature)bf.Deserialize(ms);
            protected virtual SignaturePoint[] CreateFromTT8500PointsData(string signaturePoints)
                List list = new List();
                // Do a lot of work here to change the strange format that we get
                // as a string into bytes and transform them into an array of my custom class
                return list.ToArray();
            protected void CropPoints(List list)
                foreach (var point in list)
                    point.X -= TopLeft.X;
                    point.Y -= TopLeft.Y;
                BottomRight.X -= TopLeft.X;
                BottomRight.Y -= TopLeft.Y;
                TopLeft.X = 0;
                TopLeft.Y = 0;
        public class SignaturePoint
            public int X { get; set; }
            public int Y { get; set; }
            public bool PenUp { get; set; }
            public SignaturePoint()
            public SignaturePoint(int x, int y)
                : this(x, y, false)
            public SignaturePoint(int x, int y, bool penUp)
                : this()
                X = x;
                Y = y;
                PenUp = penUp;
            public Point ToPoint()
                return new Point(X, Y);
            public override string ToString()
                return ToPoint().ToString();

    As you might have noticed, Signature is marked as Serializable, and that’s exactly what we’re doing to store the “perfect” information in the database. We call the Serialize() method on my Signature class, and store the resulting byte array in the database as VARBINARY(MAX). It works fine when we pull that back with ADO.NET and re-hydrate a Signature object with my static Deserialize() method.

    To actually draw a signature on a picture box on a Windows form for example, we call my ToBitmap() method, that is in another file (partial class), and it generates a bitmap of the requested width, height, and pen width, suitable for display on a receipt, screen, report, etc.

    However, as a limitation of our ridiculous reporting engine (and we are currently trying to work around it’s oddities), for an upcoming beta, we are trying to get SQL server to create the bitmaps for passing back up to our reporting engine. Yes, I do know that is application tier logic and shouldn’t be performed at the database level. We are still trying to work around it using custom controls with the reporting engine.

    So, I create a SQL CLR scalar function in my Signature class (SqlBytes CreateBitmap(SqlBytes serializedSignatureBytes, SqlInt32 width, SqlInt32 height)), moved Signature and SignaturePoint to a CompanyName.SignaturePad.Common assembly, added a reference to System.Drawing. I added the assembly to SQL Serverand fought it a bit (setting TRUSTWORTHY to ON for the database). I had to manually add System.Drawing as well, because the version on my computer didn’t match exactly on the server, yet another pain and indication I shouldn’t be doing this. And another indication was SQL server warning me that System.Drawing hadn’t been tested and that the universe will indeed explode if they change something in it. I accept the risks, at the moment.

    Everything worked great… for 3 signatures. As soon as the 4th signature is added, I get this:

    Msg 6532, Level 16, State 49, Line 1
    .NET Framework execution was aborted by escalation policy because of out of memory.
    System.Threading.ThreadAbortException: Thread was being aborted.
       at System.Drawing.Graphics.CheckErrorStatus(Int32 status)
       at System.Drawing.Graphics.DrawImage(Image image, Int32 x, Int32 y, Int32 width, Int32 height)
       at CompanyName.SignaturePad.Common.Signature.ToBitmap(Int32 width, Int32 height, Int32 penWidth, Color foregroundColor, Color backgroundColor)
       at CompanyName.SignaturePad.Common.Signature.ToBitmap(Int32 width, Int32 height)
       at CompanyName.SignaturePad.Common.Signature.CreateBitmap(SqlBytes serializedSignatureBytes, SqlInt32 width, SqlInt32 height)

    Looking at that code, I’m doing everything that I know to do. I’m making sure to Dispose() all Bitmaps, Graphics, Streams, etc. I even tried explicitly setting this to null. SQL Server just runs out of memory after about 3 signatures. I’m not sure if this is a side effect of its execution plan (perhaps forking to multiple workers?).

    So… now I’m stuck, and I’m not really sure what to do, until we get a workaround in the reporting engine, which someone else is currently working on. I’ll update this post if we get a working solution.

  • The Law of Circle K Registers

    Circle K LineCircle K always has way more employees working at any given time than the number of registers open. I believe they pride themselves on how long they can back the line up at the register. There were four employees there today, including the manager. I took the picture at the right today! I complained to him and asked him to open another register. He said it was shift change time and that they had to keep one register closed. The only problem with this is that it is always shift change time.

    Chris Leon and I go often for ICEE time, and at different times of the work day, but we’ve always run into this, and we came up with The Law of Circle K Registers:

    1. Let n = number of Circle K employees on the clock
    2. Let r = number of Circle K registers open
    3. n >= 2r + 1

    It’s really annoying, and I wish there was a Valero near my work.

  • MBA, Obama, and Water


    Today, I finally was able to register for LSUSMasters of Business Administration program.  I had to go to a stupid orientation that wasted 30 minutes of my life and then had to go across campus to a computer lab to waste another 30 minutes of my life where they showed people how to register.  I just needed them to unlock my Compass so I could register.  I’m not a retard so I knew which classes I wanted.

    Jenny is also starting the MBA program, though she is doing pre-requisites, so she was able to already register for her classes, and they made her go to it too.  We then went to get our new Pilot Cards made which took forever, and then to the bookstore to find out how ridiculously expensive books are.

    All in all, I paid a little over $1500 for school and $400 for books.  I hate school.


    Conservatives will believe almost anything that another one of their kind makes up about a non-conservative candidate, so it shouldn’t surprise me that people would believe absolutely fabricated lies about Obama‘s background as a "radical islamist" and things like that.

    Today at work, someone forwarded around that lie, so I did reply to all, and I set the record straight:

    That story about Obama is not true, even though it claims “We checked this out on "snopescom".”

    See the snopes article:

    Chris Benard

    Not surprising, I suppose.  I just get irritated by people spreading lies, especially when the lie even says it was verified by Snopes, which it obviously wasn’t.  People will actually believe these things without looking them up like I do.

    Shreveport Water

    Well, you know what they say about Shreveport: it’s a nice place to visit, but don’t drink the water.  We’re the new Mexico.  They don’t really say that, but they might now.  Today, we had a serious water situation.  It’s still not back to normal, and a boil advisory is still in effect.  I won’t be drinking city water for a long time.  Luckily, we have bottled water at work, and I have bottled water at home.

    Fatburger was closed when I went by today, and Moe’s was only serving bottled drinks.  Circle K wouldn’t sell ICEEs or fountain drinks.  All the children were sent home from school and won’t be returning tomorrow.  Lots of people are being affected by this, and it shows how serious of a problem it is to have a single water treatment facility.

  • My House Stinks and I’m Almost Done With School

    It smells like rotting flesh in my house.  It’s absolutely horrible.  I’ve been through this before.  Some animal has taken it upon itself to crawl up inside my attic and die.  Then my house smells like death for a week.  You’d think I’d have to do something about it, but I’ve ignored it in the past, and like all problems, that makes it go away.  I hope that’ll work that time.  Does anyone have any input on that particular problem?  (Don’t come over to my house this week for sure.)

    Not that that’s related in any way to my school, but I’ve taken half of my finals, and since I didn’t care about this semester, I’ve gotten a B in Math 201 (Discrete) and a B in English 201 (Introduction to Fiction).  I only have Management 301 (Principles of Management) and Communications 135 (Speech) left.  I’m pretty sure I’ll end up with As in those classes.  December 19th at 7pm at the CenturyTel Center, I graduate.  Yay!  Everyone is invited!

    Unfortunately, or fortunately, depending on my mood, I start on the MBA program in the Spring.  I took the GMAT and I only had to make a 450 on it since I have a high GPA, and I passed it with ease.  All in all, I liked this semester; I made some new friends and didn’t really try all that hard.  I have senioritis and I don’t care what I get; I’m just ready for this to all be over.  I’ve spent far too long in school.  Perhaps they’ll confer a PhD on me instead.

    MCPD(rgb)_504_505 In other news, I’m now certified as a Microsoft Certified Professional Developer in both Windows and Web.  I actually made a perfect score on the Windows one and a 976 on the Web one.  I’ve scheduled MCPD: Enterprise for Monday, December 17th, so I hope I pass that too.  My work is reimbursing me for all the tests, which is good, since they’re $125 each.

    Speaking of work, I ate a lot of food today.  We had a pot luck lunch for our building and everyone brought stuff, except for me.  I didn’t even think I’d be able to make it.  I had a two hour final starting at 10:30 and the lunch started at 11:45.  I actually rushed through my Math 201 final in order to make it back in time, and I got there only like 2 minutes late.  I’m a free food whore.  I even stayed a little late tonight and ate the night class (they teach support people downstairs about XP)’s food.  When I get to eat for free, I feel like I’m sticking it to the man.  Yeah, I know that makes me weird.

  • Enabling SQL Cache Dependency With Schema Solution

    My solution has now been included in a published book about ASP.NET for SQL Server

    This post is about programming and ASP.NET / Microsoft SQL Server 2005, so any of my friends who aren’t programmers won’t find this interesting. With that said…

    If you’re reading this post, it’s more than likely that you’ve encountered the “Cannot create trigger ‘dbo.” error when trying to enable a SQL cache dependency on a table. I will explain how to fix this. If you’re lazy, skip to the bottom. If you want to know the source of the problem, continue reading.

    This is the full error text:

    C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -et -E -d DatabaseName -t SchemaName.TableName -S SERVER_NAME
    Enabling the table for SQL cache dependency.
    .An error has happened.  Details of the exception:
    Cannot create trigger 'dbo.SchemaName.TableName_AspNet_SqlCacheNotification_Trigger' as its schema is different from the schema of the target table or view.
    Failed during cache dependency registration.
    Please make sure the database name and the table name are valid. Table names must conform to the format of regular identifiers in SQL.
    The failing SQL command is:

    The failing stored procedure is provided right there, and I didn’t even see that the first time, so I found what it was running in the SQL Profiler. That was stupid of me, but I still found it. The offending code in that stored procedure if you go and view it (you must enable the database for caching using “-ed” before you can see the stored procedure) is the following:

    ALTER PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] 
                 @tableName NVARCHAR(450) 
             DECLARE @triggerName AS NVARCHAR(3000) 
             DECLARE @fullTriggerName AS NVARCHAR(3000)
             DECLARE @canonTableName NVARCHAR(3000) 
             DECLARE @quotedTableName NVARCHAR(3000) 
             /* Create the trigger name */ 
             SET @triggerName = REPLACE(@tableName, '[', '__o__') 
             SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
             SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
             SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 
             /* Create the cannonicalized table name for trigger creation */ 
             /* Do not touch it if the name contains other delimiters */ 
             IF (CHARINDEX('.', @tableName) <> 0 OR 
                 CHARINDEX('[', @tableName) <> 0 OR 
                 CHARINDEX(']', @tableName) <> 0) 
                 SET @canonTableName = @tableName 
                 SET @canonTableName = '[' + @tableName + ']' 
             /* First make sure the table exists */ 
             IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL 
                 RAISERROR ('00000001', 16, 1) 
             BEGIN TRAN
             /* Insert the value into the notification table */ 
             IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName) 
                 IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName) 
                     INSERT  dbo.AspNet_SqlCacheTablesForChangeNotification 
                     VALUES (@tableName, GETDATE(), 0)
             /* Create the trigger */ 
             SET @quotedTableName = QUOTENAME(@tableName, '''') 
             IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR') 
                 IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR') 
                     EXEC('CREATE TRIGGER ' + @fullTriggerName + ' ON ' + @canonTableName +'
                           FOR INSERT, UPDATE, DELETE AS BEGIN
                           SET NOCOUNT ON
                           EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' + @quotedTableName + '
             COMMIT TRAN

    The offending code is:

    SET @fullTriggerName = 'dbo.[' + @triggerName + ']' 

    What’s happening is that the stored procedures that are created by the aspnet_regsql tool do not handle schemas, which are new to 2005. It automatically assumes you’re using the default schema “dbo”. I’ve modified Microsoft’s stored procedure to be able to handle schemas gracefully.

    All you need to do is change the DECLARE block and the “Create the trigger name” blocks to:

    DECLARE @triggerName AS NVARCHAR(3000) 
    DECLARE @fullTriggerName AS NVARCHAR(3000)
    DECLARE @canonTableName NVARCHAR(3000) 
    DECLARE @quotedTableName NVARCHAR(3000) 
    DECLARE @schemaName NVARCHAR(3000)
    /* Detect the schema name */
    IF CHARINDEX('.', @tableName) <> 0 AND CHARINDEX('[', @tableName) = 0 OR CHARINDEX('[', @tableName) > 1
        SET @schemaName = SUBSTRING(@tableName, 1, CHARINDEX('.', @tableName) - 1)
        SET @schemaName = 'dbo'
    /* Create the trigger name */
    IF @schemaName <> 'dbo'
        SET @triggerName = SUBSTRING(@tableName,
            CHARINDEX('.', @tableName) + 1, LEN(@tableName) - CHARINDEX('.', @tableName))
        SET @triggerName = @tableName
    SET @triggerName = REPLACE(@triggerName, '[', '__o__') 
    SET @triggerName = REPLACE(@triggerName, ']', '__c__') 
    SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger' 
    SET @fullTriggerName = @schemaName + '.[' + @triggerName + ']'

    If you’re lazy or you just want to go ahead and fix it, here are links to the full original Microsoft version and my modified version that works. Just run the code in my modified version after you enable the database and then you should be able to enable any table, including those that have a schema.

    If anyone finds problems with these, please let me know. I tried to test them, but it’s possible there might be scenarios for which I’ve not accommodated.

  • 1 Year Blogging Anniversary

    It came to my attention one year ago today, I started blogging. I find that humorous, considering the anniversary of my post about how I hate has its anniversary three days from now. The reason that’s humorous is that I just got done ordering some Christmas gifts from there.

    I suppose I can’t just write to say that I started blogging a year ago. It has been an interesting time, and while at times my updates were slow and far apart, I’ve kept at it. I didn’t know if I could enter that whole blogging thing, but I’ve certainly done better than others.

    A year ago I worked at a different place that had a different website. The current one is one of my design and programming. I now work for a place where I don’t dread going to work every day. I never have to talk on a phone, and I get to program all day in a much more mature programming language. I’ve gotten to work with a coworker and design a new web site for my current employer.

    My semester is finally over, which means a year of school has gone by as well. My GPA is back up to a 3.5 (I think), but I won’t graduate for another year. Hopefully, when I write a recap in a year, I’ll be talking about my graduation in a day or two. I have my grades for 3 of the 4 classes I was taking, and the ones I have are all As, and I’m hopeful the last one is as well.

    Life is pretty good right now, and I’m in a much better position than I was a year ago. I will endeavor to continue blogging when school and life allow me.

  • I Love My New Job

    If you read my blog or know me, you know I quit my job at Highland Clinic about a week ago. I also mentioned the CareerBuilder post they had up to replace me. I linked to a saved version of the post in case they changed it. They did change it. The new version (locally saved copy) is just a help desk position. In other words, the position is for someone who just answers the phone, tries to solve basic problems, and escalates problems to other staff members via the ticketing system.

    This is pretty strange, because this means one of two things. One possible decision was that they no longer need someone who can do everything I was doing, which is highly unlikely considering the unsuccessful interviews already conducted. This leaves only one logical conclusion in light of the two open positions on the career page; they are going to replace me with multiple people. I must admit that I get a pretty big kick out of that.

    In any case, I’m loving my new job as a programmer at New Tech Computer Systems, a wholly owned subsidiary of Morris & Dickson. I’m a C# programmer, and Chris and I are currently redoing the Morris & Dickson website. The resultant code will be used as the basis for future web projects. It’s a lot of work, but it’s a lot of fun. Check the site in a few weeks when it will have quite a different look.

    It’s good to be able to make money doing what I actually like to do. I’ve been programming since elementary school on a variety of systems and languages. I program projects for myself and other in my spare time, so getting paid to do it isn’t very much of a stretch. As you might be able to tell from my first blog post, I have never voluntarily worked on a weekend at any company, with the exception of yesterday. Chris and I really wanted to complete what we were doing on Friday, so we came in from about 12:30pm to 6:30pm on Saturday to finish refactoring some middle tier code. Next up, I’ll be working on some of the non-programmatic presentation layer so that we can begin work on the web forms part of the site.

    It’s very strange because the day seems to fly by extremely quickly, while at the same time, it feels like I haven’t worked for Highland Clinic in years. It’s amazing how easy it is for a new to absorb my attention so quickly and entirely when it is something that I love doing.

    On an unrelated subject, I just completed my Fine Arts 140 class and got a 95%, an A. I’m about to start my Finance 301 class next week, <sarcasm>which should be loads of fun</sarcasm>. At least my fall semester should be fun. I’ll be taking Computer Science 315, 345, 460, and 480.

  • I Quit My Job

    Today was my last day at Highland Clinic. It was not unexpected, and if you know me well, you knew about it already. I gave only two weeks notice because I didn’t know whether or not they would flip out and fire me immediately, and I wanted to at least be able to have my vacation money to hold me over between jobs. Keeping a secret like that from lots of people for more than a month is quite difficult, and unfortunately I told one person who doesn’t know how to keep a secret.

    I don’t regret leaving. I left for a number of reasons, both personal and professional. The lack of office supplies is pretty pedantic to even be talking about, but I did bring it up in my exit interview. As an employee who has worked there loyally for three years, I think I deserved my own stapler, tape dispenser, and scissors. My boss felt differently about it. It boils down to money not getting spent where it needs to be spent… in my opinion, of course. I have a limited viewpoint, that of the IT infrastructure and its effect on the business as a whole. The office supplies are merely an analog to talk about larger problems.

    The environment I worked in wasn’t very conducive to working. I had to sit in a room with a bunch of other people who didn’t perform the same job as me. I liked them, but it did make concentrating on my job harder, and I’m fairly certain it had a similar effect on them. The phone was constantly ringing, because my job duties entailed that of a lowly support person (sorry if you do support like I recently did), a programmer (all the intranet applications programming), network support (both LAN and NOS tasks), and anything else “specified by the IT Manager”. Many things were specified by the IT Manager. If you’d like to replace me, see the CareerBuilder post (mirrored here for when it’s deactivated or filled).

    Although I’ve been dissatisfied for awhile, I felt unable to voice my opinions for fear of being fired, as I felt was the case with other employees. I was able to land an excellent job doing what I love to do, programming. It’s a new language and I’ve been practicing it, but I’m sure I’ll be fine. I’ll be working for Morris & Dickson, and more specifically their New Tech Computer Systems.

    I’ve visited the facilities and my office and desk specifically. I feel it will be a much better environment, and a place where I can put my skills to dedicated use in the most productive manner possible. I think I’ll be able to get a lot of satisfaction out of seeing lots of people using something that I’ve written, or at something to which I’ve contributed code. I saw that at Highland Clinic on a small scale, but many more people are affected by Morris & Dickson applications.

    My last day was great, though. I kept pretty busy and worked through lunch and left around 3:50pm. I had a pest control appointment at 4, and I was a little late. A lot of people brought a ton of food for a party for my last day, my boss’ birthday, and Kelli’s birthday. Ashley made me pasta salad, which was great as usual, and surprisingly I enjoyed John’s wife’s chip dip a lot too. I don’t normally like guacamole based things, but Sharon’s dip was exceptional. My exit interview lasted a long time. The HR person listened to everything I had to say, but seemed more interested in defending Highland Clinic against my criticisms, rather than just listening to my concerns. I’m not saying she didn’t listen. I’m just saying I think it would be more appropriate to be less defensive, especially if the person is leaving.

    All my old contact methods remain except my old work MSN which few people knew anyway. I have no hard feelings toward Highland Clinic, but I’m not sorry I’m leaving. I’m incredibly excited about my new job, and I start at New Tech on Monday. I’ll try to blog about it before the first week is over. No promises, though.

    (Disclaimer: None of the text in this post or comments on it represent the opinions of my current or past employers. None of the postings on this site do, but I felt it expressly needed to point out that none of these things should be taken for fact or used for any official purpose. These are my opinions and perceptions.)

  • Merry Christmas Food at Work

    I’m lucky to work with a bunch of women who bring food to work all the time. We’re having one of our office parties. I always cop out by buying a 12-pack or two of Diet Cokes. I scored big time today. I got a chocolate bar, coffee mug with Hershey’s Kisses and Swiss Miss, homemade banana bread, and a lottery ticket from our CFO, Debbie. In that last link, my boss Jay makes an appearance too.

    Also, a huge thanks to Ashley (middle desk) for making some of her circular noodles and sauce stuff for me. I love that stuff. What a great present!

    My Christmas Food at Work
    Merry Christmas Food!

    It makes having to work today almost worth it.

Posts navigation