Programming

  • Using Reflection to Dynamically Generate ToString() Output

    Update: Added code to show “null” when a property is null, like Visual Studio does.

    If you’ve ever used Visual Studio in any iteration in any language, you have most likely used the immediate window. It’s insanely useful and lets you just type an instance of an object and if the object doesn’t have an overridden ToString() method (I’m back in C#/.Net world here for the purposes of this post), Visual Studio will dynamically generate output for you, so that you can see the current state of the object.

    Here is what Visual Studio’s output looks like on an object I have:

    {CompanyName.CreditCard.Processor.CreditDebit.SaleResponse}
        base {CompanyName.CreditCard.Processor.Response}: {CompanyName.CreditCard.Processor.CreditDebit.SaleResponse}
        AddressVerificationSuccess: true
        AuthorizationCode: null
        AuthorizedAmount: 50
        CvvSuccess: false
        ReferenceNumber: null

    However, what if you want to be able to have ToString() generate this type of output for you, for the purposes of debugging, like using with Debug.WriteLine or Trace or any other logging framework? It is useful to be able to see later what the state of an object was during a problem situation. Sure, you can write your own ToString() code in every object to generate its state in string form, and then aggregate all the base objects using base.ToString(), but that is a lot of iterative, repetitious coding.

    My classes are simple data transfer objects and look like this:

    public class SaleResponse : Response
    {
        public virtual bool AddressVerificationSuccess { get; set; }
        public virtual bool CvvSuccess { get; set; }
        public virtual string AuthorizationCode { get; set; }
        public virtual string ReferenceNumber { get; set; }
        public virtual decimal? AuthorizedAmount { get; set; }
    }
    
    public class Response
    {
        public virtual bool Success { get; set; }
        public virtual string ResponseMessage { get; set; }
        public virtual string TransactionID { get; set; }
        public virtual string GatewayResponseCodeText { get; set; }
        public virtual string IssuerResponseCodeText { get; set; }
        public virtual GatewayResponseCode GatewayResponseCode { get; set; }
        public virtual IssuerResponseCode IssuerResponseCode { get; set; }
    }

    As you can see, Visual Studio didn’t even give me the contents of the base class, but I want this information. My ToString() method I’ve created in the SaleResponse object is the following:

    public override string ToString()
    {
        var flags = System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.FlattenHierarchy;
        System.Reflection.PropertyInfo[] infos = this.GetType().GetProperties(flags);
    
        StringBuilder sb = new StringBuilder();
    
        string typeName = this.GetType().Name;
        sb.AppendLine(typeName);
        sb.AppendLine(string.Empty.PadRight(typeName.Length + 5, '='));
    
        foreach (var info in infos)
        {
            object value = info.GetValue(this, null);
            sb.AppendFormat("{0}: {1}{2}", info.Name, value != null ? value : "null", Environment.NewLine);
        }
    
        return sb.ToString();
    }

    Which generates the following output:

    SaleResponse
    =================
    AddressVerificationSuccess: True
    CvvSuccess: False
    AuthorizationCode: null
    ReferenceNumber: null
    AuthorizedAmount: 50
    Success: True
    ResponseMessage: null
    TransactionID: AXB234234
    GatewayResponseCodeText: null
    IssuerResponseCodeText: null
    GatewayResponseCode: ExpiredDevice
    IssuerResponseCode: Error

    What I like about this approach is that it grabs all the base object’s properties recursively. Obviously, there are performance concerns with doing reflection, so I wouldn’t use this constantly, but I think it’s a good way to write out the state of an object in the event of an error. Also, I made the example simple for this blog post, but obviously, you could put that code into an extension method for System.Object or just into a static utility class, adding an object parameter and replacing “this” with the name of the object parameter.


  • Strong Name an Assembly Without Source Code

    Because I program in the 1970’s, part of what I do is programming against modems and serial ports. Specifically, we send medical claims over modem to adjudicators, in the event of unavailable internet access. As a customer, you’ll appreciate having your pharmacy delay you for 20 seconds rather than saying “Sorry; you can’t have your medicine.”

    While taking a break from disco dancing, I was trying to add a strong name to one of our projects. One of the requirements, however, for strong naming is that all of the assemblies you reference must be strong named as well. All are, except one: COMM-DRV/Lib.Net. We use this assembly to talk to the modems, because it abstracts a lot of the serial communications and allows us to program against a logical “modem”.

    I called the vendor, Willies Computer Software Company, and he said that we’d have to have the source license to strong name, but I asked why they don’t strong name their own binary distribution. He just kept saying they don’t do that, and we’d have to buy the source license. We didn’t need to make changes to their code, though; in fact, I already did that by inheriting their class and hiding some methods with new ones.

    So, I set about trying to find out how to strong name an assembly for which you do not have the source. The short answer is “you can’t.” The longer answer is “You can’t, unless you disassemble it, reassemble it, while signing at the same time, and only if it’s not obfuscated.” I finally found the answer I was looking for at geekzilla.

    Here are the steps you need (back up your old assembly):

    Create a Key Pair

    This is only necessary, of course, if you don’t already have one generated for yourself or your company.

    sn.exe -k C:\Path\To\KeyPair.snk

    Disassemble the Assembly into IL

    ildasm.exe CdrvLibNet.dll /out:CdrvLibNet.il

    Reassemble the Assembly from IL While Signing

    ilasm.exe CdrvLibNet.il /dll /output=CdrvLibNet-StrongNamed.dll /key=C:\Path\To\KeyPair.snk

    The Result

    Thankfully, everything worked great, because their assembly was not obfuscated (it’s basically a .Net wrapper around their C++ unmanaged, native DLL product, so no real intellectual property to steal here). Now, we have their product strong named with our public key, and we are able to build on top of it with a strong named product of our own.


  • How Insensitive

    Update: Added section at the bottom detailing what not to do.

    As part of the updater process that I wrote for a current project, a “boot strapper” program queries the database for available versions, and if there is a newer version available, it deletes the current program folder, replaces those files with the new files, and then executes the main program executable. If there are no newer versions, it simply executes the main program executable immediately. This gives the user they are executing the program itself, instead of the “invisible” boot strapper, but we are able to easily manage updates in this manner.

    Obviously, if you are a .Net developer, you know about the venerable app.config, which is used to set program parameters at run-time, rather than at design time. Because these can contain changes to our connection strings and custom configuration sections, we wanted to preserve this and other special files that may be used in the future. As our solution, we created a whitelist of files that we do want to replace, that looked like this:

            ///
            /// Extensions of files that may be deleted during uninstall/reinstall.
            ///
            private List _deletableExtensions = new List(
                new string[] { ".exe", ".dll", ".pdb", ".chm", ".manifest" });
    
            private bool canDeleteOrOverwrite(FileInfo file)
            {
                // Only delete files with specific extensions.
                bool canDelete = _deletableExtensions.Contains(file.Extension);
    
                return canDelete;
            }

    Before anybody murders me, we camel case private methods and underscore prefix and camel case private class-level variables here. As you can see, this will allow deletion of executables, class libraries, debug symbols, help files, and manifests. However, we had a problem in my code that didn’t surface until yesterday. We were trying to figure out why one file out of all the files in the directory was remaining an older version, “ActiveReports3.DLL”.

    What may be obvious to the reader, especially considering the title of this post, is that my List<T>.Contains() check, by default, is case sensitive, and “.DLL” != “.dll”. This required quite a bit of stepping through code to find, as I wrote this code a long time ago. A simple press of “Ctrl+Shift+Space” revealed that there was an overload of IEnumerable<T>.Contains(T item, IEqualityComparer<T> comparer).

    Because I’ve had to do this in the past, I knew that, because my T in this case was string, all I needed to do was use the StringComparer, which implements the IEqualityComparer<string> interface. Because it is filenames, and we are working with Visual Studio generated files, we care neither about culture nor case in this instance. Here is the revised, working version:

            ///
            /// Extensions of files that may be deleted during uninstall/reinstall.
            ///
            private List _deletableExtensions = new List(
                new string[] { ".exe", ".dll", ".pdb", ".chm", ".manifest" });
    
            private bool canDeleteOrOverwrite(FileInfo file)
            {
                // Only delete files with specific extensions.
                bool canDelete = _deletableExtensions.Contains(
                    file.Extension,
                    StringComparer.InvariantCultureIgnoreCase);
    
                return canDelete;
            }

    That’s it! That tiny change fixed our issue and allowed the boot strapper to overwrite that file. It’s amazing that a simple little omission like that can lead to such a strange problem manifesting itself. Everybody makes mistakes; I just thought I’d showcase one of my errors and how I fixed it.

    After talking to one of my coworkers about this post, he mentioned something that we have both seen done to “work around” this particular problem, which should not be done. This is what I have seen before in others’ code:

            ///
            /// Extensions of files that may be deleted during uninstall/reinstall.
            ///
            private List _deletableExtensions = new List(
                new string[] { ".exe", ".dll", ".pdb", ".chm", ".manifest" });
    
            private bool canDeleteOrOverwrite(FileInfo file)
            {
                bool canDelete = false;
    
                // Only delete files with specific extensions.
                foreach (string currentExtension in _deletableExtensions)
                {
                    if (currentExtesion.ToLower() == file.Extension.ToLower())
                    {
                        canDelete = true;
                        break;
                    }
                }
    
                return canDelete;
            }

    This creates a LOT of strings on the heap in the process is and is very inefficient. It’s much better to let .Net handle itself and just let it know whether you care about culture and/or case sensitivity.


  • A Day Late and a Property Declaration Short

    I know this is a bit late to the game, but this morning, as I’m refactoring a bunch of old code to be shared with a new project, I’m cleaning up the C# 2.0 property declarations we all know and love:

        public class SaleResponse : Response
        {
            protected bool _addressVerificationSuccess;
            public virtual bool AddressVerificationSuccess
            {
                [DebuggerStepThrough]
                get { return _addressVerificationSuccess; }
                [DebuggerStepThrough]
                set { _addressVerificationSuccess = value; }
            }
    
            protected bool _cvvVerificationSuccess;
            public virtual bool CvvSuccess
            {
                [DebuggerStepThrough]
                get { return _cvvVerificationSuccess; }
                [DebuggerStepThrough]
                set { _cvvVerificationSuccess = value; }
            }
    
            protected string _authorizationCode = string.Empty;
            public virtual string AuthorizationCode
            {
                [DebuggerStepThrough]
                get { return _authorizationCode; }
                [DebuggerStepThrough]
                set { _authorizationCode = value; }
            }
    
            protected string _referenceNumber = string.Empty;
            public virtual string ReferenceNumber
            {
                [DebuggerStepThrough]
                get { return _referenceNumber; }
                [DebuggerStepThrough]
                set { _referenceNumber = value; }
            }
    
            protected decimal? _authorizedAmount;
            public virtual decimal? AuthorizedAmount
            {
                [DebuggerStepThrough]
                get { return this._authorizedAmount; }
                [DebuggerStepThrough]
                set { this._authorizedAmount = value; }
            }
        }

    This code is, of course because I wrote it, wonderful and has no flaws, and I used the prop snippet to create the private variable and public property getter and setter. However, I have all those DebuggerStepThrough attributes in there, due to the lovely fun of stepping through code that references properties. That avoids stepping in and out of the property declarations.

    Thank the flying spaghetti monster that now, in C# 3.0 (and of course 3.5, 4.0 and on), Microsoft has given us auto-implemented properties. This is now the equivalent code, avoiding the stepping in/out of the get/set and not requiring the declaration of a private variable:

        public class SaleResponse : Response
        {
            public virtual bool AddressVerificationSuccess { get; set; }
            public virtual bool CvvSuccess { get; set; }
            public virtual string AuthorizationCode { get; set; }
            public virtual string ReferenceNumber { get; set; }
            public virtual decimal? AuthorizedAmount { get; set; }
        }

    When I first learned this trick, I was extremely thankful to be able to do this as an even shorter shortcut to using the prop snippet and it makes the code a lot prettier. What I didn’t immediately realize is how to control scoping. Let’s say, for instance, I want that AuthorizationCode property to only be able to be set from inside the class itself, and I only want the AuthorizedAmount property to be able to get set from inside the class and inherited classes. I can then change those declarations like this:

        public class SaleResponse : Response
        {
            public virtual bool AddressVerificationSuccess { get; set; }
            public virtual bool CvvSuccess { get; set; }
            // This can now only be set from inside the class
            public virtual string AuthorizationCode { get; private set; }
            public virtual string ReferenceNumber { get; set; }
            // This can now only be set from inside the class and those that inherit from it
            public virtual decimal? AuthorizedAmount { get; protected set; }
        }

    This allows you to control the scoping of the getter and setter independently. By default, they inherit the visibility of the property declaration, in this case, public for all. I’m always thankful for the tools Microsoft gives me to make my life easier and to give me more time to spend on the actual work, rather than installing plumbing.


  • 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:

        [Serializable]
        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
            {
                get
                {
                    return BottomRight.X - TopLeft.X;
                }
            }
            public int Height
            {
                get
                {
                    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;
            }
        }
    
        [Serializable]
        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.
    System.Threading.ThreadAbortException:
       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.


  • 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:
    dbo.AspNet_SqlCacheRegisterTableStoredProcedure

    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) 
             AS
             BEGIN
             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 
             ELSE 
                 SET @canonTableName = '[' + @tableName + ']' 
             /* First make sure the table exists */ 
             IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL 
             BEGIN 
                 RAISERROR ('00000001', 16, 1) 
                 RETURN 
             END 
             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 + '
                           END
                           ')
             COMMIT TRAN
             END
    

    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)
    ELSE
        SET @schemaName = 'dbo'
    /* Create the trigger name */
    IF @schemaName <> 'dbo'
        SET @triggerName = SUBSTRING(@tableName,
            CHARINDEX('.', @tableName) + 1, LEN(@tableName) - CHARINDEX('.', @tableName))
    ELSE
        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.


Posts navigation