Posts filed under 'Uncategorized'
The code resulting from this misadventure can be found at https://github.com/agglerithm/ClrIntegration .
The whole thing started innocently enough. In our efforts to implement Syteline as a viable ERP system (now 4 years past due), I learned about an event system that was available (for a modest configuration fee, of course), one which could be used to broadcast MSMQ messages whenever a database record was changed. This was potentially game-changing for us; it would allow us to achieve true command/query separation without having to write our own ERP system from scratch.
There was only one problem: We were using MassTransit to handle MSMQ traffic, and the message format used by Syteline was unlikely to be compatible.
I weighed several options, such as writing some sort of anti-corruption layer to convert from one message format to another. I soon realized it would be necessary to look into the guts of Syteline’s event system to see how it worked.
From what I could tell, the system made use of triggers to communicate with MSMQ. I cringed at the thought of adding yet another layer of triggers to an already over-burdened design (each table already had three to five triggers attached to handle business rules). Then I decided that, if I was going to use triggers anyway, I should implement my own and at least have control over the performance. In addition, I could ensure that the messages were in the correct format from the beginning and the anti-corruption layer would be unnecessary.
It seemed like a good idea, especially when reading the glowing descriptions of Microsoft’s great new technology to replace the old C-style extended procedures: CLR integration.
CLR integration provides an interface between SQL Server and the .NET runtime. According to Microsoft, it is a wonderfully efficient and elegant dance of synchronization between two members of their family, each with an intimate understanding of the other, and with great care taken to use only the resources required for a given operation, and no more.
According to Microsoft. I suspected the truth was somewhat different.
It’s no secret that the needs of the .NET team at Microsoft are rarely considered by the teams working on such projects as Office, Windows, and SQL Server. There are .NET assemblies to interact with these things, but one gets the feeling that all the interaction is a result of hacking on the part of the .NET team, and is not borne from a sense of mutual cooperation. My work on this project has done little to dispell that impression.
So here’s how I needed to use CLR integration: Anytime a DML command is run on a table I need to keep track of, a .NET assembly should be called with the inserted or deleted record. The handler should then run a simple routine that builds a message from the affected record and sends it to the appropriate queue. From there, it’s another application’s responsibility to post the changes to the reporting database.
It’s as simple as that. Communication is completely asynchonous, so SQL doesn’t have to wait for any sort of response before it continues on to do whatever it is that it does. It therefore should be less of a resource-hog than if it were a normal Transact-SQL trigger that simply updates the report database. It would also make constantly polling the tables for changes, an even greater waste of resources, unnecessary.
All that was needed was to implement CLR integration on the application (.NET) side, then make the .NET assembly available on the SQL side. The first part was easy, the second part less so.
At first I simply wrote a trigger that would do nothing more than return a message back along the connection pipe saying that the assembly had been successfully called. There was a bit of a learning curve in making this happen, but I soon was able to demonstrate that it was at least possible.
Then, I had to make the assembly actually do something. Knowing that it should be as lightweight as possible, I decided to dispense with all my usual dependencies, including MassTranit, and using only the standard GAC assemblies and a reference to one additional dll: The Microsoft message queuing assembly, System.Messaging. Sure, it would require a little hacking to simulate the behavior of MassTransit, but the functionality I needed was not that extensive.
It was here that the fun began. I soon learned that, in order for SQL to recognize the latest changes to my own assembly, I would have to constantly blow away the trigger or triggers with dependencies on that assembly, re-install the assembly, then re-create the trigger or triggers. I also learned that since System.Messaging was not considered part of the core .NET runtime, it would have to be added to the SQL Server assemblies list.
When I added that, I learned that there were further dependencies that would not allow me to install System.Messaging until THEY were installed. It was about this time that I was hit with an epiphany similar to the sudden realization by a child that Santa isn’t real.
The wonderful engineers at Microsoft, the lofty geniuses responsible for the .NET system, were suddenly revealed as being mere mortals. As the different dependencies added up, I realized that they had made…let’s say, architectural decisions that were indistinguishable from careless blunders.
System.Messaging could not be installed without first installing System.Windows.Forms. The Forms assembly, in turn, required various assemblies related to drawing rectangles and other aspects of the GUI environment. These, in turn, required references to still more dependencies.
My only question was..Why? For God’s sake, WHY?
What kind of bumbling incompentent includes dependencies on the user interface in a mid-level assembly which, by definition, operates BELOW the user interface level? And WHY is that bumbling incompetent making major architectural decisions at Microsoft?
It was, of course, POSSIBLE to include System.Windows.Forms in my ever-growing list of depencies in SQL, but I was beginning to feel increasingly uncomfortable with this. Especially when my attempts to add the last few assemblies I needed failed with an out-of-memory error.
At first I thought this error was an issue with SQL Management Studio, but it quickly became apparent that it was the actual server that was out of memory. After a little research, I found that this was a well-known problem with CLR integration. On one message board, the comment was made: “Microsoft really boned us good on this one.”
Great. Wonderful. Now I wasn’t sure what I was going to do, beyond hacking into System.Messaging itself and try to implement its logic without the crazy dependencies.
That was, of course, not an option at all. It was crazy to go to this much trouble to work around what was clearly a poorly-designed system.
I decided to do it anyway.
I used JetBrains’ dotPeek to peer into the inner workings of System.Messaging. Soon, I discovered that this fairly complex library was actually a wrapper for a few calls to an old C-style DLL called “mqrt.dll”, located in the system32 folder.
Soon, I had a renewed feeling of hope. All I needed to do was to figure out what the different methods of the DLL did, then co-opt the ones that I needed to create messages, open a connection to a queue, and send the messages there. I didn’t have to read queues or even deserialize messages.
As it turned out, all I needed were the following methods:
MQOpenQueue(), which provided a handle to a message queue;
MQSendMessage(), which puts a message into a queue with a given handle; and
MQCloseQueue(), which closes the queue.
Microsoft’s assembly included two main objects for dealing with queues and messages: The Message object and the MessageQueue object. I copied these classes verbatim to my project and began systematically hacking away at the parts I didn’t need…which was most of them. At first I pared them down a little too much…it turned out that I needed to include a security context before I could send a message…but eventually I ended up with a lean, mean facsimile of System.Messaging that did what it needed to do.
Now all I had to do was emulate MassTransit. This wasn’t as difficult as it sounds, since I didn’t have to bother with subscriptions or any of the other higher functionality of the framework. I simply had to ensure that the messages I was sending were in a format recognizable by MassTransit.
There was another advantage to the lean and simple approach I was taking, which was that the messages were hierarchichally flat. There were to be no complex members, only primitives, and it was simple enough to create a tool to build a message header defining these types. I created a number of extension methods for building the attributes I needed, such as:
public static string GetBooleanDefinition(this object spec)
{
return “System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″;
}
I then created an XmlMessageEnvelope class to serve as both a container and builder for the actual Xml that would go into the message. The class made use of reflection to take an arbitrary class and build a message from it that could be recognized by MassTransit. My system would eventually have many message classes to accommodate the changes I needed to handle, and I needed it to be flexible.
So that’s it, so far. I’m putting quite a bit of thought into this part of the process, so that when I add the back-end logic I don’t have to worry about what layer had introduced which bug at whatever point in time. I still have quite a bit of work ahead of me, and I anticipate more wrestling with the Microsoft stack, but there is at least a light at the end of the tunnel.
August 10th, 2011
Recently, I was asked to delve into a third-party application used by our tech support team to keep track of end user requests. Several years earlier, I had hacked into this old, classic-ASP web application and set up a round-robin system so that the tickets would be distributed evenly among the techs instead of being assigned by request type or priority. This ugly hack had worked for about a year, but when the new version of the software was installed, it blew away all my changes.
Now that I had a little free time, I was asked to look at it again. This time I vowed to encapsulate all the logic in a separate .Net assembly so that if any future upgrades erased my changes, it would be a simple matter to plug them back in.
Unfortunately, what I had in mind took for granted that the ASP code would be somewhat de-coupled in its architecture. That wasn’t actually the case. The block of code that assigned the tech had dependencies all over the place. To make matters worse, it didn’t appear to be working correctly, anyway. Currently, it assigned every ticket to the same person, who then had the responsibility to reassign the tickets to his collegues.
I decided that I would forget about how it was working now, and simply focus on getting it to work the way we wanted. I created a web service that would look at two tables, one of which had a list of techs and a boolean field indicating whether or not they were currently in the queue. The second held one row containing the ID of the next tech in the rotation. When one called the web service, it returned the ID of the next tech, then changed the value in the “current tech” table to prepare for the next call.
I probably should have simply written another ASP script to do this, because as it turned out, I had to write such a script anyway.
Wanting to minimize changes to the existing code, I replaced a call to the critical subroutine with one in a separate ASP file that called the web service to get the next tech ID. Since the application wanted a name, not the ID, I then had to use that ID to get the name from the existing system. I also had to filter out the requests that were being made to Human Resources rather than tech support and treat them differently.
Calling the web service from server-side was tricky. I ended up using something akin to AJAX, but without the A, the J, or the X. After some trial and error, I was able to make the web service calls with consistency.
I got the code working fairly well, but when I set up a test harness to see how it would look to the end user, I was astonished to find that every ticket was still being assigned to the same tech as before (let’s call him the “static tech”). I broke the processes down as much as possible, verifying that every step was completing correctly. I ran a sniffer on the HTTP traffic and saw that the correct value was being returned by the web service.
While I was puzzling this out, I noticed something strange. The page loaded with the static tech selected in a drop-down, but when I looked at the HTML source I saw that the expected tech was actually the default. There was javascript running after the fact and changing the value on the page.
I changed the javascript, and fixed the problem…but this was only for the branch of execution that was running because I was registered as a tech. End users would see something different. Therefore, I changed my permissions so that when I ran the test it would work in a way consistent with the end users’ experiences.
It should have worked with no problem, so naturally it didn’t work at all. Once again, every ticket was being assigned to the “static tech”.
It made no sense at all. I pondered the problem for a moment, remembering that if something seemed to make no sense, it was in fact my invalid assumptions and not reality that was causing the confusion. So, I looked at the facts.
First, in end user mode, the application did not give the user the opportunity to change the tech assignment. The tech value was in a hidden field instead of a drop-down.
Second, I had disabled the javascript that had caused the problem before, so that couldn’t be the issue. In fact, this was a moot point, because in this mode the value was hidden and thus there was no reason for javascript to fiddle with it.
I concluded that, as unlikely as it may seem, there was another process interceding between the assignment of the tech and the rendering of the next screen. I just had to find it.
I went through the code line by line, jumping from one ASP file to another as I tried to chase down the distributed spaghetti logic. Finally, I came to what I was able to determine was a call to a COM object…in fact, the javascript I had disabled made a call to this same object.
The purpose of this call, according to the documentation, was to override whatever decisions had been made by the earlier logic and substitute whatever value was produced by this mysterious, black-box process.
To sum up, the EXPECTED behavior of this system was as follows: First, find the appropriate person to assign the ticket to based on request type and priority. Second, forget all of that and replace this value with whatever is returned by the COM object. Finally, just in case either of the first two didn’t take, replace whatever is on the screen with yet another call to the same COM object via a short javascript block.
Oh, but it gets better.
I went into the file system and tracked down the COM object in question. To my astonishment, I found that it was a .Net assembly!
So…classic ASP was being used as a massive, convoluted shell over a few .Net assemblies being used as middleware. I could accept that. The company that developed this software was probably moving gradually from 15-year-old technology to 10-year-old technology. Nothing wrong with that.
But then I looked at the assembly in Reflector.
The object that was being called was a classic God Module, with hundreds of methods and properties. It did everything, from making ticket assignments to tracking hardware to washing and folding shirts. I tracked down the method that I needed to look at, and found something like this:
string GetTechnician()
{
return juniorGodModule.GetTechnician();
}
So I went to the definition of juniorGodModule…and found something virtually identical to the God Module. Once again, there were hundreds of methods and properties. Once again, it seemed to do everything.
But, at least, it was encapsulated.
Within juniorGodModule, I found this method:
string GetTechnician()
{
return juniorJuniorGodModule.GetTechnician();
}
Fortunately for my sanity, the definition of juniorJuniorGodModule was hidden. Unfortunately, the logic behind the static tech assignment would remain a mystery.
As a last-ditch effort to understand what was going on, I ran a SQL trace to find out where the pseudo-COM object was getting the returned value. Here is what I found:
SELECT name from Techs where ID = 19
So. For some reason, known only to the God Module and its little nested junior God Modules, the correct tech for any situation was always #19. Don’t ask me why. It wasn’t the first value on the list under any sorting scheme I could fathom. As far as I could tell, it was a completely random selection, without the randomness of being…you know…DIFFERENT each time.
I settled on an ugly, ugly solution to the problem. After going to the trouble to encapsulate my logic in a web service, I was now forced to change two ASP files in addition to adding a third. But, at least it is working. For now.
I haven’t mentioned the name of the system in question, for obvious reasons. However, this is the type of problem you can expect when venturing into the bowels of any ERP system. Enter at your own peril.
June 1st, 2011
Just when I think I’ve got something figured out, I suddenly realize the issue is much more complex than I thought.
My customer order hack of Macola had been working flawlessly (well, almost flawlessly) for years. Now, suddenly, everything was going wrong.
The problem was that we had added a new customer to the automation system that had two conditions we’d never needed to address before. First, state sales tax had to be added to the order. Second, they ordered only kits rather than individual parts.
I’ll start with the second one.
Everyone has their own way of defining kits and parts, depending on their own business rules. Macola, however, has a built-in hierarchy:
A kit is made up of one or more finished parts.
A finished part is made up of one or more subcomponents.
A subcomponent can be made up of one or more pre-assembled subcomponents. In fact, it’s subcomponents, all the way down.
At the very bottom of the hierarchy lies raw materials, from which subcomponents are derived.
A unit of raw material can be the same as a subcomponent, which can be the same as a finished part if it is buy/sell and requires no assembly.
Finished parts are easy. You don’t have to worry about what a finished part is made of (most of the time). The cost is in there, the price is in there, the inventory location is in there.
Kits are a completely different story. You can’t go to the inventory location of a kit to get its cost or available quantity. It doesn’t exist until it’s ready to ship, at which time it is quickly assembled and shipped out.
The normal rules for creating an order in Macola took me some time to figure out when I first started experimenting with it five years ago. The steps are:
- Get the next order number from the order entry control file (OECTLFIL_SQL) and increment the table entry.
- Create a record in the order entry header table (OEORDHDR_SQL) with order type “O” and using the order number obtained from step 1, and matching records in OEPDSHDR_SQL and OEINQORD_SQL.
- For each line item, create a record in the order entry line table (OEORDLIN_SQL).
- Also for each line item, update the inventory by modifying the location table (IMINVLOC_SQL) to show the number of items allocated, and enter a record in the inventory transaction table (IMINVTRX_SQL).
- Complete the order by updating total sales, total cost, total weight, total dollars, etc., from the line item tables. Set selection code to “C” and OEPDSHDR_SQL’s status code = “O”.
Simple, right? Actually, there are other details I haven’t included here, such as locking the control file table before updating it, inserting line comments, and logging the activity. Adding kits to the process made my task considerably more complicated.
Step 3 above assumes that you’ve already got the relevant information about the item the customer is ordered. Details like the item number, description, unit of measure, cost, price, quantity available, etc., would be included here. At some point before the order is created, another process has derived all this information from the customer request.
This “black box” process had to change when kits were involved. Instead of simply going into the item master table (IMITMIDX_SQL) and the item location table (IMINVLOC_SQL) to get details and available quantity on the part, we must look at the kits lookup table (IMKITFIL_SQL) to see what finished parts are included in the kit. Then we must look up each of those parts and determine the cost of the kit (presumably the price is not the same as the sum of these parts, and is associated with the kit and not the component parts).
Since a kit may contain more than one of each part, the formula for determining the cost of a kit is: Sum(part cost * quantity per kit).
Now we have the information we need for the order entry line table. Inventory is a different matter, though. Step 4, which updates the inventory for each line item, had to be extended like this:
- For each kit, insert a record in the inventory transaction table with the cost of zero.
- For each part that comprises the kit, insert a record in the inventory transaction table with the quantity of (quantity per kit * number of kits) and the cost associated with that part.
- Update the inventory location table to record the quantity allocated both for the kit and for each of the component parts.
So how do you handle the “each part” part? It was important to de-couple the inventory lookup from the order creation, so I needed to know what parts were in a kit BEFORE I was ready to create the order. I ended up adding a collection to the PartEntity that essentially held a list of component parts. This list was populated when I got the inventory item data, using a kit query object that issued the following SQL command:
SELECT avg_cost, K.seq_no , qty_per_par, comp_item_no
from <<legacy>>.{0}.dbo.IMKITFIL_SQL K inner join <<legacy>>.{0}.dbo.IMINVLOC_SQL I
ON K.comp_item_no = I.item_no where K.item_no = @itemNum
(Where “<<legacy>>” is the server name and “{0}” is the database name.)
The sticky thing about the kit problem, for me, was that the consequences of handling kits incorrectly didn’t make themselves known for several months. Financials and inventory numbers were off as a result of this problem, which fortunately didn’t involve one of our larger customers, but still brought the wrath of the bean-counters upon my head.
As if things weren’t bad enough, I then had to deal with the tax issue for this same customer. I had accounted for taxes in my code, getting the tax rate from the tax table (TAXDTL_SQL) and adding the rate times the sales amount to the total.
It didn’t work for several reasons. Downstream processes in Macola expect certain combinations of field values to be in the order header table, and they are unhappy if they don’t get them.
After painstaking analysis of the Macola processes, I was able to determine:
- The “tot_tax_amt” field needed to be zero if the tax rate was zero, and total sales if the tax rate was greater than zero.
- Likewise, the “tot_tax_cost” needed to be zero if no tax, total cost otherwise.
- The tax amount (total sales * tax rate) went into the “sls_tax_amt_1″ field (There were several fields for different taxes; luckily I only had to use the first one).
- Finally, the “tot_dollars” field needed to be the total of sales + taxes.
I ended up handling some of this in code, calculating cost and the total sales amount minus tax, then passing this value into the SQL statement that finalized the customer order.
UPDATE [<<legacy>>].[{0}].DBO.[OEORDHDR_SQL] SET
selection_cd = 'C', tot_sls_amt = @tot_sls, tot_tax_amt = CASE WHEN
isnull(tax_pct,0) = 0 THEN 0 else @tot_sls END, tot_cost = @tot_cost,
tot_weight = @tot_wgt, tot_dollars = @tot_sls + isnull(tax_pct,0)/100 * @tot_sls, tot_tax_cost = CASE WHEN
isnull(tax_pct,0) = 0 THEN 0 else @tot_cost END,
SHIPPING_DT = Automation.dbo.fnDateToEDIDate(getdate()) ,
sls_tax_amt_1 = isnull(tax_pct,0)/100 * @tot_sls
WHERE ( ORD_TYPE = 'O' AND ORD_NO = @ordNo )
Problem solved! For new orders, at least. I still had to fix the existing orders that were missing sales tax by issuing credits for each affected order from the previous four months, then create invoice orders with the correct tax so they could be re-sent.
As busy as I was, I really couldn’t complain about all this extra work. I had brought it on myself.
Now all I have to do is figure out to reproduce this black magic in our new ERP system, Syteline. That’s a story for another day.
July 29th, 2010
There’s nothing I hate worse than a poorly-worded error message. Case in point: The one in the title.
It came up when I was trying to configure Syteline. Syteline’s help file was no help, neither was Google or Bing. What is a “DefaultType”? What is the significance of the value “21″? How do you define it? What linkage are they talking about?
OK, I was able to figure out that last one. The error came up when I was trying to link the Objects database with a configuration. It gave me this cryptic error whenever I tried.
I tried creating a new Objects database, but got the same result. I tried linking to a known good Objects database on another server. No luck.
Actually, yes luck. This gave me an important clue: The problem was not with the Objects database. I was with one of the others, probably the applications database.
So, I tried it again, this time while tracing the SQL activity. Sure enough, the configuration app was looking for the value “21″ in a table called “DefaultTypes”.
I looked for DefaultTypes in the apps database, and there it was. I was able to copy over the record from a working server to the test server I was trying to set up, and hey presto! I was back in business.
At least, I am back in Normal Syteline Hell rather than Cryptic Syteline Hell as I was a few minutes ago. One step at a time.
May 18th, 2010
In my position as a developer at my current job, I am frequently called upon to remedy the shortcomings of various third-party systems the company has, for better or worse, invested in. Since many of these systems are widely used, it might be beneficial for others to learn from my experiences. Hence, the blog.
May 1st, 2010
