Category Archives: Coding

Great Moments in Database Design

Coding,

I am by no means an expert DBA, but the design mentality behind a product that my company uses leaves me scratching my head.  This product which shall go unnamed (Exact JobBOSS) has a variety of faults, but none more perplexing than their ‘double keying’ of tables.

Now I’m not talking about a compound key here.  No, this is where they’ve gone through and put the key in twice, such as this example from the ‘Address’ table:

JobBoss Address Key
No problem they’re the same (green), until they’re not! (red)

The logic behind this design decision is somewhat puzzling. I guess they figure that a user may want, for example, a new customer to have an old customer’s Address number? Not only does that not make any sense on it’s face, but A) The user has no control over how this number is assigned and (the kicker) B) This second key is allowed to be null within the database. Only the application rules keep a catastrophic event from occurring in making sure that this second ‘key’ is not null.

Now you might be wondering: what’s the big deal if this second key is null, after all, there’s a primary key on the table already. Here we get to square the circle of this puzzling design decision because as it turns out other tables do not ‘key up’ to the primary key of the table, but the second, fake, ‘key’. Here’s the create code from a table that ‘keys up’ to the Address table (emphasis mine):

ALTER TABLE [dbo].[RptPref] WITH NOCHECK ADD CONSTRAINT [FK_Addr_on_RptP] FOREIGN KEY([Address]) REFERENCES [dbo].[Address] ([Address])

Not [Address].[AddressKey], but [Address].[Address]?!?*  Obviously there’s two issues that immediately spring to mind in such a situation, both of which the developer is attempting to control strictly through their Franken-code.  The first is, like I stated above, the field can be null.  What if this record is not inserted properly and it is null?  Fun times I’m sure!

The second issue is that since the field is not a key, there can be more than one record with that same value.  Is their code tight enough to prevent this possible corruption issue?  Like much to do with this product, you’ll have to take it on trust!

Addendum: I should also point out a basic programmatic issue with this whole design methodology.  If I want to modify records in a table which contains a column that is referenced as a key in a different table, but isn’t a key in the source table that I want to modify, there’s no (easy) way to catch this.  In the back of my mind, I’m thinking that maybe the database was designed as a complicated mess in order to make it semi-proprietary.

*(I should point out that the database for this product is small enough that it’s feasible to make a (tiny) entity chart of it within Visual Studio; but beware that easily half the tables will appear to have no relationship with any other table because of situations like the above (‘keying up’ to a possible null non-key, that’s actually never supposed to be null)).

**(While I ranting about JobBOSS I figured I’d bring to light the package’s great love of the ‘rtl70.bpl access violation’ errors that it is prone to getting.  I long thought that it was an issue with my installation, but I’m now convinced that they’ve coded the Delphi portions with little to no error handling (is there no ‘try/catch’ in Delphi?)).

Suppressing Duplicate Crystal Labels

Coding,

For our file folders at the end of the year we would run a Crystal report that would find all the vendors who were issued payments over the year.  These labels would only consist of the vendor name and number (and the fiscal year, but that was handled with a text and parameter field).  However, after a large upgrade our Mas90 install (to version ~4.something), the report would duplicate some vendors, and not print others at all.  Digging into it I discovered that the report was set to use a table that nominally tracked AP vendor payments per period and that in the past this table kept a cumulative total for each vendor over the course of a year.

In the new version though, the new periods were broken out by months, essentially making the table a dupe of the AP check history table in Mas90.  Now when the report was run, a label would be printed for every check issued to a vendor, and as a filter the report left payments out that were less than $100 (which was fine for a whole year, but bad for each month!).

I poked around and after consulting a couple different websites I came up with the following fix:

  • I put the vendor number field into a group
  • I suppressed the group header and footer sections (right click->suppress (no drill down))
  • Went to Report->Selection Formulas->Group
  • Added the following formula : {AP_CheckHistoryHeader.CheckDate}=Minimum ({AP_CheckHistoryHeader.CheckDate},{AP_CheckHistoryHeader.VendorNo})

What the last line does is it only picks out the first date that the vendor appears.  This can still lead to duplicates if the vendor was issued more than one check on that same day, but it’s much better than getting twelve labels for many of the vendors!

(By the way, there might be a better way to do this as I don’t know too much about Mas90, and even less when it comes to Crystal Reports; but I was given the evil eye for mentioning that I could do this up in .Net or, even worse, with an Excel/Word mail merge where the ‘remove duplicates’ function had been used in Excel).

Nortel BCM Log Parser

Coding,

I had originally coded this project in VB and never got it into a distributable format.  For example, I had all the file paths and database connection strings hard coded.  After educating myself a bit on C# I recoded the application in that language, and have the program using LINQ instead of (oof!) OLE.  I also now have it coded to accept values specific to a user’s environment via an XML settings file.

What the program does at this point is that it reads the call detail recording logs that are stored in a directory and commits them to an MS SQL/SQLexpress database.  For my own purposes I have all the default settings set on the BCM for recording the log files, so if you’ve changed any of these settings this program may not work.  As well, I have my logs FTP’d every day to a server share that I can access via UNC (i.e. \\server\logfiles).

I have a little setup menu within the program so that you can create the database, set the settings, and then write the settings out to an XML file for future use. Using the XML settings file the program can be scheduled to run if the XML settings file is given to the program as a variable (ex, NortelLogParser c:\settings.xml).

In the future I hope to develop a web front end for the database, but for the time being you’ll have to use Excel or some other tool to query the database for the information that you want.

Click here to download.

UPDATE: I just noticed (12/14/2011) that the field detection is still a little buggy, and a feature that I added after the fact of associating a record with the file it came out of lags by a day.  The program is still better than nothing, but I do have to patch it up a bit when I get some time.

Command Line Argument

Coding, ,

Firing up the way-back machine, I recalled interfaces from Exchange versions past that would immediately return some nice data, such as mailbox size, item counts, etc.  I set out to try and recreate some of these interfaces, but it seemed that a lot of the coding examples were in C#, a language that I wasn’t very familiar with.

Undeterred, I went through some C# lessons and I now have what I hope is a decent “working IT guy’s” knowledge of it’s inner workings.  Unfortunately at this point, I can’t exactly remember what I was missing from the past Exchange interfaces that drove me to learn C# to begin with, apart from the table that had the mailbox sizes.

I eventually determined that the Powershell command that I needed was:

Get-MailboxStatistics -server <exchange server name>| Select-Object DisplayName, TotalItemSize, ItemCount,StorageLimitStatus,LastLogonTime | Out-GridView”

I figured my C# coding skills would have to apply to something else as I could just as easily put that line into a batch file! For some reason, though, launching the command from a batch file would open the ‘Grid View’, but it would close immediately, so back to C#

I had the program working fine on my desktop, and had it set to either ask for a server name, or accept a command line argument that would contain the server name. For reasons I still don’t fathom, the command line argument wouldn’t work on the Exchange server itself. I eventually had to change the code from:

if (args[0] != null)
ServerName = args[0];

to:

if (args.Length>0)
{
if (args[0] != null)
{
ServerName = args[0];

Which makes more sense, I guess, but I don’t know why the former only worked on the desktop unless there’s some variation of the .Net runtimes between the desktop and server in how they handle command line arguments?

Anyway, if you’d care for a little program that auto-launches this grid view, I’ve posted it here.  It includes the user’s display name, their mailbox size, the number of items, and if they’ve exceeded their quota.  You may need this note if you get an ‘index out of bound’ error due to the Powershell visual GUI elements not being installed (obviously you’ll need a 64 bit machine with the Exchange Powershell add-on installed).

Remote Desktop With XP

Coding, ,

Faced with an aging remote access server with dubious patches I wanted to move a little more quickly to a Windows Server 2008 R2 terminal server, but there is an issue: there’s no support for NLA connections within XP out of the box.  True, there’s some registry hacks to enable the feature, but I couldn’t have end user’s doing that, so I resolved to write a program to do it for them.  It wasn’t until I was almost done that I discovered that, alas, Microsoft had already written one.

I hate to be outdone to that extent so I’m still posting mine here: a zip file which contains a Windows forms version, and a command line version, both of which run remarkably faster than Microsoft’s patch.  My program(s) will check to make sure that it’s the proper version of the OS (Windows XP with service pack 3) and that the registry entries need to be added (with visual output).  No backups are taken, so use at your own (minimal) risk!

(Although I typically share my code, I must admit to being slightly embarrassed at it’s condition since it’s my first full fledged C# program.  I’ll share if I get around to cleaning it up so that it doesn’t look like a VB guy wrote a C# program 🙂

Now to patch up all the other Windows XP issues with the Remote Desktop Connection services in Windows 2008 r2….