Careers and Cheap Furniture - Some Assembly Required

I read a lot of great material from numerous technical blogs every single week. Sometimes I am stirred enough to comment on them, but only rarely am I inspired to blog about something I read on another blog. Steve Jones’ recent editorial is one of those. To summarize:

I think it’s important for management to provide a good example for workers in a company. They need to show that not only are workers not easily replaceable “resources”, but that they are also appreciated and respected.


Building a team, building teamwork, and creating the synergies from people working together is hard. It takes an investment, and it doesn’t come from company meetings that preach some vision statement. It comes from truly interacting with your employees, showing them respect, and helping them march with you towards a common goal.

How the workplace should work.

Let me first say that Steve Jones is right, and I respect him a great deal. Respect, loyalty, and teamwork should be very important in today’s workplace and it behooves employers to remember that. It would be nice to be able to work for one company for decades and retire happily. However, even if employers should behave this way, many do not.

What does this have to do with cheap furniture?

Code for food.

Don't be this guy.

Bad economic times tend to strip the veneer from these relationships and the ugly reality of particle board shows through. When economic times get tough, your employment options tend to decrease. If the employee has less options and opportunity, the employer simply doesn’t need to treat them as well in terms of pay and other benefits. Employers often act accordingly. This isn’t evil, it’s just human nature. While in a perfect and just world employers would respect employees, pay them fairly, and treat them with dignity; we unfortunately must live in the real world.

A couple of “M2M friends” of mine learned this the hard way recently. One is being laid off from his current employer for 5 days at a time. Why only 5 days at a time? Well, if you are laid off more than 5, unemployment benefits are due. The other admin had to take a pay cut or lose his job as well. Not every employee at his company had to endure a pay cut however.

These experiences are likely to become more typical in the era of The Disposable Worker.



What can I do about it? I don’t want to be cheap furniture.

People in general, and employers in particular, will only treat you as well as they have to. Therefore, the only way to protect yourself from mistreatment is to increase your value as an employee. Increased value, and for that matter visibility, will counter balance the loss of options due to a terrible economy. Do you really think that Paul Randal, Mike Hotek, Brent Ozar, or for that matter Steve Jones worry about their jobs? No, they have opportunities galore. When approaching someone of this caliber, you don’t interview them as much as they interview you. There will always be a job for any of these guys as long as they stay sharp, which is why they are above “mistreatment.”

So, what should you do about it? Run twice as fast. Learn, blog, network, anything to increase your value and visibility. Expanding your skill set and networking are truly the only security any of us have in a recessive economy.

How to Wreck Your M2M Database in Three Easy Steps

My company shuts down over the holidays, but I received a frantic email from one of the companies we support. The user told me that M2M had deleted fiscal year 2009 and he was unable to fix it. To be honest, I thought the user was mistaken because I knew that you could not delete an accounting year with postings in it. In fact, every single month besides December were closed as well. A quick call to one of M2M’s best tech support reps confirmed that there is no way to do this. However, upon investigation, I determined that the user was correct. Somehow, M2M had deleted fiscal year 2009, but I had no idea how.

M2M deletes fiscal years? What are the three easy steps?!?!

Well, after actually speaking with the user, and reconstructing things, I figured out what happened. Unless you have a test server to work on, you’ll just have to trust me that this actually works as you do not want to do this to your production database. If a user opens the Accounting Years Screen (AYRS) and navigates to the current year, they cannot delete it and will receive the error below.

AYRS Delete

This is what should happen.

However, they can delete a fiscal year in the following three steps:

  1. Click Modify on the Accounting Years screen.
  2. Change the Thru Date and From Date to next year’s date.
  3. Click Save and watch the magic happen.

M2M actually issues deletion statements to the GLRULE table for every month of the current fiscal year. I documented all of this using SQL Profiler. I will post a video in the future on the use of SQL Profiler for M2M administrators. When you look through the profile trace you will see the following statement passed 12 times (one for each month):


So, how did you fix it?

Well, I was very fortunate in the timing of this event. As I said, we shut down during the holidays. This user was working during the shutdown. I was able to restore the entire database from back up and fix the problem without losing any data at all.
The one saving grace is an error triggers the first time a user attempts to post a transaction to the now deleted fiscal year. Therefore, the problem should be quickly discovered. Also, the only table changed is the GLRULE. Microsoft SQL Server does not support the restore of a single table (third party products like Quest Lightspeed can). However, you could restore the back up from the previous day to as a different named database and then append the records to the damaged table in your production database.

What was M2M’s response?

Well, when I showed the M2M Tech exactly what happened, he was as shocked as I was. He tested version 6.0 and it still has the flaw. Therefore he issued a change request and is pushing for the fix to be included in the 6.01 release of M2M which is supposed to be released this spring.

There is one other thing I learned about while troubleshooting this issue and I will share it with you in the article below.

Did You Know That M2M Logs Deletions?

In troubleshooting the problem in the above post, I came across an undocumented (as far as I know) procedure to log details about deleted records. I’m not referring to the ECM auditing available from the CSPROD Screen.

What is the M2M Delete Log and how does it work?

The M2M Tech showed me a table called M2MDELETELOG in the M2M database (versions 5.X and later) which logs deletions from tables. I was thrilled to learn this because the normal M2M audits are very limited. M2M logs the deletion date, workstation which called for it, and a text field which typically includes the user name, table, and screen involved. I traced the deletion of the accounting years through this table in the last problem.

Upon further digging, I found a stored procedure called LogDeletes which actually inputs the values.

That’s great. Is there a catch?

Well, unfortunately M2M did not go far enough with this. Take a look at the LogInfo field for a sample deletion in which I deleted a sales order:

Unqualified M2MDELETE by USER in SOMAST from form frmSo Parameters: NONE

What’s wrong with that? Most of the pertinent information is in one field. The user, table, and screen are all in that one text field and must be broken out to have statistical value. I know the user deleted a record in SOMAST, but I can’t determine which one, so it would have been nice to have the identity column of the deleted record. This of course has limited value because the identity column tells you nothing pertinent. You would have to examine a previous back up and track back to the useful record identification such as the sales order number.

Furthermore, there is a problem when you examine the stored procedure code. Can you spot it?

CREATE PROCEDURE LogDeletes (@LogInfo varchar(256))
	DECLARE @RecCount int, @Min int, @Max int, @Target int
	SET @RecCount = (SELECT COUNT(*) FROM M2MDeleteLog)
	IF @RecCount > 20000
		SET @Min = (SELECT MIN(RecordID) FROM M2mdeletelog) 
		SET @Max = (SELECT MAX(RecordID) FROM M2MDeleteLog)
		SET @Target = ((@Max - @Min) /2 ) + @Min
		DELETE FROM M2MDeleteLog WHERE RecordID < @Target
	INSERT INTO M2MDeleteLog (UserName, UserSPID, LogDate, Workstation, LogInfo)

The point is that most of it is entirely unnecessary. Other than the insertion statement, everything else should be accomplished using a scheduled job. Each and every time you delete a record in M2M, it assigns variables and checks the record count of the delete log. This wastes time and should only be done on a periodic basis.

So what? How much time would that waste? Well, after I stripped the insert statement from it and ran it on my server it took about a 1/10th of a second every time it ran. In the month of December this process ran almost 8000 times for just one of the companies on my server. This may sound excessive, but if you delete one sales order with 10 line items which have 10 releases each, you have deleted in excess of 100 records. So, in just one of my companies almost 15 minutes of time that month was wasted. Time is money, and this doesn’t account for the wasted memory being used by the variables either.

Even as the code is written, it isn’t written well. Since the cap is 20,000 records, all the system needs to do is take the last RecordID, subtract 10,000 from it, and delete anything older than that. I appreciate the effort at clean up, which does not occur with the ECM audits, but I wish it was better implemented.

A Possible Future Project

As a DBA, one of my biggest concerns is one of my users becoming disgruntled and deleting important records in M2M. Imagine the following scenario. Patty in Sales becomes unhappy with the company and looks for another job. After she secures one, she starts deleting random open Sales Orders in M2M over a period of a few hours. How long would it take to detect the problem? If your company is typical, I would guess several weeks. Since my company has sales orders which remain open for years, it could take longer than that. If you discover three weeks later that you have missing sales orders, Patty has already left and you have no recourse. You are going to have to manually audit every single sales order, check to see if it’s still in your system, and re-enter them with new sales order numbers. It is way too late to perform a system restore.

Therefore, I’m thinking about creating a scheduled job to export this table, extract the values out of the LogInfo field, and check the resulting table for suspicious behavior on a regular basis. The job could run every 15 minutes or so, check the prior time period’s deletes, and notify the admin via e-mail when some threshold was reached. If I did so, I would of course share it on the blog.

What do you folks think?

Manually Deleting a Database in M2M

I am a big fan of using practice companies in M2M, which allow users to experiment with M2M while not creating problems in production. However, occasionally M2M will not properly delete a company when it is no longer needed.

Recently this happened again, so I thought I would document the process of manually deleting a company for anyone with a similar problem. I need to be clear on one point. I am not suggesting you manually delete companies just because you can. Always attempt to use M2M to properly delete practice companies first. If that should fail however, you can use the following. This would be a good place to include my standard Disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

Perform the following steps:

  1. Make a back up of your UTIL folder by simply copying it elsewhere in Windows explorer. Make sure ALL users are out of m2m including any non-user accounts such as bar coding.
  2. Open SQL Enterprise Manager and locate the database you wish to delete. Right click on it and select delete.
  3. If you have backups for the practice company you must delete them as well. You can locate them through Windows Explorer.
  4. Open Visual FoxPro and enter the following.
    set excl on
    open data X:\m2mdata\util\util     && Replace X with your mapped drive. 
    delete conn  &&  This will delete your connection string. See picture below.
    use UTCOMP excl
    brow   && Select the appropriate database record and mark it for deletion by clicking the small rectangle to the left.
    Close All

    If you neglect the delete conn step, you will receive an error message: -1 could not update system database. I mention this specifically because I often forget this step.

  5. There are also user access records related to the company you need to delete in UTACC table. Enter the following into VFP:
    use utacc excl
    delete for fccompid='XX'   && Replace ‘XX’ with the company number you wish to delete.
    Close All
  6. Navigate to x:\m2mdata\data00\loading where x is your mapped drive and run createdbc. Select your live company from the top portion. Important: You must select the option “Rebuild System DB Connection” to rebuild your UTIL connection string. Remember to repeat createbc for each remaining company.

That’s how you manually delete a database in M2M. Any Questions?

Accounting Summaries – The Report!

Spaceballs - The Flamethrower!

Spaceballs - The Flamethrower!

The best dog I’ve ever owned also had an interesting name, Peeve. She was my mom’s pet Peeve. Anyway, I’d like to share a pet peeve I have in regards to report names. I cannot tell you how many people I’ve met that actually put the word “Report” into the title of the Report.

Vendor Summary Report
Sales Report for Part Number 123
Weekly Repair Metrics Report

Ugh. We know it’s a report. It’s redundant and a waste of time to include the word “Report” in your report. This is something else I learned through the pain of red ink from Mr. Girard.

After all, you don’t see best selling authors creating book titles that end with “The Book.”

Under the Bus

monster_school_busRecently we had an M2M outage at work, which caused annoyance and downtime for many of my users. It took the better part of a day to investigate and solve. As you can imagine it was a stressful time, executives were angry, etc; which was particularly difficult for me because I am a perfectionist when it comes to my work.

I must admit that I was especially irritated when I learned it had nothing to do with myself or M2M. When my counterpart, found his error and we started bringing users back online, an executive cornered me for an update.

What did I tell him? “Bob did _____ and it caused _____?”

No. I said, “Bob found it. It was a problem with M2M VBA and he managed to ferret out the answer by checking…. “

I never mentioned that Bob caused the problem with his new image distribution method. I focused on the troubleshooting and recovery effort rather than play the blame game.

Why am I telling you this? Well, my executives don’t even know I blog so they are very unlikely to read this. I just wanted to urge my readers (both of you) to never throw someone under the bus. It isn’t productive and is in fact disastrous when you work on a team.

Later, when my technical supervisor was back in the office he asked me about it. I gave him the facts, focusing on the solution and the good troubleshooting by Bob. He grinned and said, “He found the problem… because he caused it.”

I returned the grin and responded with, “Well, yeah.”

Can You Farm?

mitch_hedberg_edited”As a comedian, I always get into situations where I’m auditioning for movies and sitcoms, you know? As a comedian, they want you to do other things besides comedy. They say “alright you’re a comedian, can you write? Write us a script. Act in this sitcom.” They want me to do shit that’s related to comedy, but it’s not comedy, man. It’s not fair, you know? It’s as though if I was a cook, and I worked my ass off to become a really good cook, and they said “alright you’re a cook… can you farm?” – Mitch Hedberg

The other day one of my good friends asked me, “Dave, how much would you charge to create a simple website for my buddy so people could order his stuff from the web?”

I tried to explain that although I do have some web development experience, I haven’t really touched that in two years and I wouldn’t be able to do it. Her friend could not afford to pay me while I re-learned to do it, and he should seek out a professional.

My friend then continued to argue with me that since I’m a geek, a super-geek in her eyes, that I should be able to do it. She just kept asking me how much. I guess this whole notion that computer professionals can do anything with a computer stems from the belief that what we do is magic. Since they don’t have any idea how we do our jobs, they think that we can just cross over into jobs that seem related to them.

As computer professionals, we may laugh privately at such requests, but are we not guilty of similar thinking? In the past year I’ve read articles regarding The Great DBA Schism and problems with Microsoft’s BI Certifications that further suggest more separate DBA roles.

I’m not suggesting that I have the solution to these issues, but I agree that more attention should be brought to them and further delineation is necessary to accurately describe our database roles. If SQL Server DBAs continue to refer to ourselves simply as DBAs, is that not the equivalent of asking a great chef if he can also farm?

BI Ethics

The ethics of business intelligence is a touchy subject that many people don’t want to talk about. However, more attention in the past several years has been given to this subject mostly due to ethics scandals at huge companies such as Enron, Tyco, and WorldCom. The Sarbanes-Oxley Act came about because of such scandals and the billions of dollars they cost investors as well as the decrease in investor confidence in the stock markets, but it only applies to publicly held companies.

This is all well documented, and related information can be found all over the web. Recently a Code of Conduct for DBAs was proposed by SQL DBA, Brad McGehee. This is an excellent, albeit broad set of guidelines for DBA behavior.

My last article discusses a relatively benign situation where a decision was made how to calculate and provide statistics. In this article, I’d like to focus on business intelligence. What do you as a Business Intelligence Expert do when asked to provide questionable information? I’ll give the following example to facilitate discussion.

The Accounting Manager of your organization requests a report which details Accounts Receivable Aging. You code the SQL for the report creating groups of invoices that are 30 days, 60 days, 90 days, and more past due. You include an attractive chart and send the report to the Manager for approval. He reviews it and confides in you that his new bonus structure (or perhaps even his job) is dependent upon these numbers. He requests that since ACME Rockets is always late, Wile E. Coyote is always out in the desert chasing the Road Runner instead of paying bills, that they should be removed from the report. His reasoning is that no matter how well the manager performs his job, ACME will never pay in less than 90 days and this drastically lowers his rating.

Think about that for a second…. I’ll wait.

Now let’s suggest that the Accounting Manager is your direct supervisor. You may actually lose your job if you refuse. Look deep within yourself, did the answer change?

Anyway, the following poll is anonymous. What would you do? Feel free to explain why you answered the way you did in the comments.

What would you do?

View Results

Loading ... Loading ...

When Being Right May be Wrong.

Recently I was tasked with automating a reporting process which collects repair metrics. The current method of tracking repairs in my company was tedious and time consuming. As is typical, the method used to track this was Excel Hell. Every week someone would run the Received Goods (RPREGO), Inventory On-Hand by Location (RPINOH), and the Monthly Shipment Analysis (RPMSHA) reports. They would export them to Excel, pivot table the information, and then type all of that information into a master spreadsheet which created multiple graphs for executive review.

Why was Monthly Shipment Analysis used rather than Invoiced Sales (RPIVSL)? Well, they want their information by product class and RPIVSL doesn’t pull that information.

Anyway, when I substituted an invoicing script instead, I matched their overall totals for repair revenue to the penny. However, the average repair cost on the invoicing script was quite a bit higher than they received from the Monthly Shipment Analysis. Upon investigation of our business processes I determined the cause. Often when a part is returned to us for repair, there is nothing wrong with it. Sometimes we will test the units gratis, and if no fault is found, return them to the company free of charge. These units are shipped, but no invoice is created because the dollar value is zero. When figuring averages their method has a greater quantity of repairs than mine (because it is derived from shippers, not invoices), so their average is less because those repairs are for $0. Make sense?

Luckily the manager acknowledged that by their own definition these units were not repaired, and so they went with the invoicing numbers with a footnote as to why the numbers were not consistent with statistics. However, the manager risked giving the executives the impression that his reports could not be trusted.

What do you think? Is consistency more important than accuracy?

My next article will discuss the ethics problems facing Business Intelligence Experts.

“What is your Biggest Weakness?” The Classic Interview Question.

The Bobs

This is the point where we all lie and list a strength and tell the interviewer that our biggest weakness is that we just work too darn hard or some other such tripe. To be honest, I don’t know why they ask this question and what they really expect for an answer.

However, I will freely admit one of mine here. I love to code. When given a project such as a difficult report, I will often start writing code. This is especially true if I anticipate the code will be difficult or tricky.

How is a love of coding a weakness? Well, it’s because when I do this I often end up coding the same project several times. Why does this happen? One of the reasons it happens is that I often put the logic into my SQL script when that should be done in the presentation layer.

Always begin a report project with the delivery medium in mind.

When a user asks me for year to date incoming sales for customers in Dallas, TX my brain spits out the following:

SELECT SOM.fcustno AS [Customer Number]
     , sum(SOR.forderqty * SOR.funetprice) AS [Net Amount Ordered]
FROM M2MDATA01.dbo.soitem SOI
      INNER JOIN M2MDATA01.dbo.sorels SOR
      ON (SOI.fsono = SOR.fsono)
AND      (SOI.finumber = SOR.finumber)
     INNER JOIN M2MDATA01.dbo.somast SOM
     ON (SOM.fsono = SOI.fsono)
WHERE ltrim(rtrim(SOM.FCITY)) = 'Dallas'
AND   ltrim(rtrim(SOM.FSTATE)) = 'TX'
AND   year(SOM.FORDERDATE) = year(getdate())
GROUP BY SOM.fcustno

However, this is not what I need. If you review the above code, you will notice that I only pulled totals per customer number. What if the user wants to be able to drill down into particular customers and see which products they are purchasing? I need to list all the sales for these companies and then group in the presentation layer, in this case Crystal Reports. Therefore the user will be able to drill down into the data, while at the same time seeing their subtotals. Re-writing this short script is trivial, but many of them are not.

Just for reference the script I would use for this would be something like:

     , SOM.FCUSTNO as [Cust No]
     , SOM.FCOMPANY as [Company Name]
     , SOM.FCUSTPONO as [Cust PO No]
     , SOR.FENUMBER AS [Item No]
     , SOR.FPARTNO AS [Part No]
     , SOR.FORDERQTY AS [Qty Ord]
     , SOR.FUNETPRICE AS [Net Price]
     , (SOR.FORDERQTY * SOR.funetprice) AS [Net Amount Ordered]
FROM M2MDATA01.dbo.soitem SOI
      INNER JOIN M2MDATA01.dbo.sorels SOR
      ON (SOI.fsono = SOR.fsono)
AND      (SOI.finumber = SOR.finumber)
     INNER JOIN M2MDATA01.dbo.somast SOM
     ON (SOM.fsono = SOI.fsono)
WHERE ltrim(rtrim(SOM.FCITY)) = 'Dallas'
AND   ltrim(rtrim(SOM.FSTATE)) = 'TX'
AND   year(SOM.FORDERDATE) = year(getdate())

Anyway, coding before planning is one of my biggest weaknesses. What is yours?

Just for fun, I am going to tag Brent Ozar and Jorge Segarra. I hope they’ll answer this questino as well as tag others in the SQL Blogosphere.

Page 18 of 33« First...10...1617181920...30...Last »