Happy Thanksgiving

Last year about this time I referenced the famous WKRP Thanksgiving Day Episode and listed those things for which I am thankful. Well, I’ve decided to update my list for this year as a lot has changed since then.

  • I’m thankful to have a secure position in this awful economy and that my career is moving in a positive direction.
  • I’m incredibly thankful to the SQL community and their willingness to freely share their knowledge. The following offer excellent blogs and/or recorded videos with amazing free content. In particular I’d like to thank Brent Ozar, Grant Fritchey, Andy Warren, Steve Jones, Tim Mitchel, Brian Knight.
  • I’m thankful to live near two great SQL Server User Groups, especially for the excellent classes given by Mike Hotek (which he does free) at the Fort Worth User Group.
  • Once again I’m thankful that Made2Manage has not outsourced their support, and I hope they never do.

What are you thankful for?

M2M Tech Support Gaffes

As a follow up to my last post in which I said that you need to be careful following the free advice given by another customer; you should also be wary of what M2M Tech Support tells you. They can and do make mistakes. I can’t tell you how many times they’ve given me directions which were for the wrong version of M2M as well as a SQL script which was incorrect and would have caused huge problems with my database.

For example, when I first started working with a Visual FoxPro version of M2M (3.1 I believe), I had problems with one of my invoicing tables. There are as many as 3 files with each VFP table. The first file contains the data, the second contains any indexes, and the third contains memo fields. The Tech told me to delete the file which contains the index structure so that it could be rebuilt, but gave me the extension of the data file. I asked her to verify the file and extension she wanted me to delete and then did so. Of course, this was a mistake. Shortly thereafter I found out that the company we paid to do network maintenance and check our back ups was not doing so, and I had to restore an invoicing table from two weeks back. I then hired a VFP programmer to update the table using information from related tables. This was stupid on my part, but as I said I was a complete beginner and had no idea.

Later, when I first started with Crystal, I was instructed to use the SA account, the same account most M2M installations are configured to use. This is a bad idea for several reasons, but as I said, I was a beginner at the time. However, I did not know the SA password, so Tech Support showed me how to change it…. during the work day… with everyone in M2M. I specifically asked, “Will this cause problems for my users in M2M?” The tech assured me that no, it wouldn’t. Well, the results were predictable, exactly 1 minute after doing it, I had 3 calls in rapid succession from users getting all sorts of SQL Connection Errors. Nice.

Recently, I received a script I was told to run by a Senior Tech Support Rep and it began with:

--Change the database name before executing this query
use mercury
--Find inonhd records without matching inmast records

Obviously I am not going to change the name of our production database with users in the system, nor does it make any sense to change the name at all. Most of their scripts will tell you to “use DataXX” and this is more clear.

After I questioned the support tech about that, she verified that I should just run the scripts against M2MData01. I didn’t understand the reason for one of the update statements, so I checked it out as a select statement first and it returned almost 3000 records. If correct, this would indicate a serious problem in my database. I immediately stopped and called the Support Tech and she apologized that the script was in error and should not be run against my database. If I had run the update script, it would not have been an easy fix.

As a general rule, make a copy of your M2MData folders before changing anything. Do not assume your backup will suffice. It’s much faster to “restore” from a duplicate copy right on the drive than messing around with tapes. If I had simply done this I would not have found myself in a terrible spot with my first example.

This post isn’t meant to jab at M2M. These problems are not always their fault. Unless they are looking at your screen they are relying on you for the answers to their questions. Having helped people out this way, I have a new appreciation for just how difficult it is to guide a user through a process that you cannot watch.

Anyway, the point is that M2M does not administer itself, and if you are going to administer it you need to learn it. Don’t blindly follow directions as even the best Support Technicians make mistakes.

Have any of you encountered similar issues?

Want to Drop Support?

Since I began this blog a little over a year ago, I’ve had many people contact me for help and advice. I do my best to help them if I can, but one type of message never ceases to amaze me. It goes something like this:


“Can you please help me? I am on M2M Version x.x and had a hard drive/server crash. After attempting to re-build the system I get the following error: (Completely unhelpful obscure error message and code removed)

We have been down since last Friday and have not been on M2M support for several years. Can you help me get my system back up?



P.S. If you don’t help me, I’ll set your blog on fire.

The advice I’m about to give may sound a lot like my post on M2M Migration. If you don’t have a Visual FoxPro and SQL Server expert on site, and your company’s productivity means anything to you, then you need to be on support.

People who read this blog know that I am not employed nor sponsored by M2M. I am not a Made2Manage (Consona) apologist and I criticize them when appropriate. While I realize that M2M Support is expensive and times are tough, it costs money to do business. That’s just the way it is.

If you drop support, you ARE support. It’s an awful position to be in when your ERP system is down, you have no idea how to fix it, and you aren’t on support. I was in that situation years ago when I first started M2M. You can’t access their bulletin boards, and the only other one I know of is a Google group for the Los Angeles User Group. They have some good people there, but they have their own jobs to attend to. Your emergency is not their emergency, and you won’t get an instant response. Also, take any advice you get from someone other than M2M Support with suspicion as well. Free advice is often worth just what you paid for it.

Are you ready for the responsibility of being your own tech support? Even if you are able to get help with your system from another customer, was it worth all of the down time?

Even if you have a SQL and VFP expert on site, you still need to be able to upgrade your systems. Software is a never-ending treadmill and even if you are satisfied with your current version of M2M, it will not necessarily work with new versions of SQL Server or Windows Operating Systems and eventually you will be forced to upgrade.

The customers who contact me in this situation have indicated that Consona’s terms are draconian (their words, not mine), and I’m sure that is what drives a customer to spend days or weeks looking for a solution. Perhaps someone from M2M would like to clarify their policy in the comments.

Amazed by the Wrong Things

Ms Pacman

I love this chart, which I borrowed (stole) from Coding Horror. A major project that I completed recently reminded me of it. I created a sales order auditing system that included scheduled SQL jobs, multiple triggers, VBA for M2M, and Crystal Reports. The project took me a long time to complete and was highly rewarding.

However, when I delivered the report to the user, he was less impressed than I expected from such a large project that was very challenging. He asked for a pie chart at the top of the report.

Five minutes later, I returned with a full color, 3-d pie chart that is very easy to create in Crystal Reports. The guy was thrilled and has since sung my praises.

This is all well and good, but it just serves to remind me that most people have very little idea of what we do or how we do it. The project was a success due to thorough planning and development, which insured data accuracy and efficient code. All the user can see is the pretty pie chart.

Do you ever feel this way?

Oh well, at least I like pie.

Would You Use a New Made2Manage Forum?

I started this blog to help people learn more about M2M, SQL, and Crystal Reports. The reason was simple; I had to learn this stuff the hard way, by myself and essentially in a vacuum. I wanted to help anyone who found themselves in the same situation.

Since then, I’ve had a couple of people ask me, “Dave, when are you going to host a forum for M2M users?” Well, I’ve been reluctant to do so for a few reasons.

  1. I don’t intend to do M2M forever and I don’t wish to set myself up to moderate forums regarding it indefinitely.
  2. Regarding moderation, I wouldn’t be able to moderate it during the day.
  3. Bandwidth is not free, but people don’t like to see ads either.
  4. Most importantly, I’m not sure there is enough demand for it.

However, the current options are not very successful either. Let’s examine them a moment:

M2M’s own forum. I look at the Administrator’s board fairly regularly. However, the interface is from the early 90’s with frames and the functionality is counter intuitive as well. Therefore, it is hardly used. I’d estimate that they receive less than one post per day.

In fact, I’d bet money that Consona doesn’t read their own forums either. If you look in the “Company Setup/Maintenance” forum, you’ll see the second recent post at the top entitled “Remove Permissions from ALL of Us…” In that post another forum member suggests that the original poster delete all records from the UTSEC table. UTSEC contains the list of current M2M screens and deleting anything from it would be catastrophic. If someone from M2M was paying attention, they’d remove that statement immediately. Did I mention that the dangerous advice has been there for nearly eight months?

Another disadvantage is you must be on M2M support to use the forum. With the current economic climate, many companies just can’t afford support fees.

I realize that economic times are tough, and that maintaining a decent forum costs money. However, if the M2M forums were free and being used, calls to support would drop dramatically. In the long run, I think it would save money for Consona.

The Los Angeles Area Made2Manage User Group. This is a Google Group for users in the LA Area, and some of them use it since dropping M2M Support. This is a free service and the group members are very helpful, but the group still doesn’t have a lot of traffic anyway. Also, I don’t care for Google Groups (list groups in general) because of the way they process messages. Comments accumulate and get re-sent over and over again. For example, if Milton posts a question about the Purchasing screen the message goes out to the subscribers. If Sally responds, her message includes both hers and Milton’s. After three more people reply to his question, the messages get longer and longer and the signal to noise ratio becomes intolerable. However, I would like to mention again, that the users are good people and I’ve received assistance from several of them.

I’ll end this post with a poll. If someone creates and maintains a modern forum site for M2M and related technologies, how often would you participate?

How often would you participate?

View Results

Loading ... Loading ...

Pop Quiz

Dennis Hopper - Speed

Dennis Hopper - Speed

You get a frantic phone call at 3am on a Sunday. It’s your boss, and there’s been a fire. The server room, and perhaps the whole building, is still smoldering. He asks you the following questions:

  • Is our data safe?
  • Is it off site? Is it secure or on someone’s kitchen table?
  • When was the last time you checked the back up? You are checking them, right?
  • Are you sure you can restore the M2M files? You have been performing test restores, right?

There are many blog articles on the web about the importance of disaster recovery, and you’ve probably read a few of them. I won’t re-hash everything here, but do you have the answers to all of those questions? If not (and you value your job), stop reading this and address them immediately. I’ll wait….

Good! Now that the above are covered lets address some M2M specific issues.

  • Can you build your SQL Server and install everything without help? Servers seem to prefer to die on weekends, and M2M Support is not available late at night or on weekends and holidays.
  • Where is your software? Latest Service Packs? Software keys? At the bare minimum, a M2M server requires Windows Server, Visual FoxPro, SQL, and Made2Manage.
  • Do you have documentation handy with all of your M2M and other settings?
  • Do you have instructions ready to install all of your optional modules? Do you have the latest install files for them? I recommend keeping all of this (including the install files for everything) in electronic format with your back-ups if possible.

I remember watching a movie where special forces soldiers spent hours assembling and disassembling their rifles blindfolded. The point was that they became intimately familiar with their weapon and knew everything about it. Ultimately, this made them more effective soldiers.

Recently I wrote disaster recovery instructions for SQL Server, Made2Manage, and the associated modules for my current employer. One of my fellow employees performed the same process on a Domain Controller and Exchange Server. We then verified them by restoring everything from bare hardware, back-ups, and our instructions in an isolated room without access to the internet. Our manager also put us under stress by telling us our results were going to be public and that we had better not let him down.

Why a closed room and stress? It simulates a disaster. Can you be assured that you will have access to the internet in a disaster? Even if you could, would your boss want to wait while you download service packs? Since we had all of the files and had practiced, we built and tested our servers in less than 4 hours.

What are the benefits of a complete test?

  • To prove we could do it.
  • I learned exactly what makes M2M work.
  • I’m now confident that I can fix anything on that server.

Incidentally, with the exception of our database server, the rest of our servers are replicated in two other sites in the United States. In the near future, our SQL Server will be replicated the same way. So we should never actually be “under the gun” so to speak.

So Hotshot, what do you do?

The Terrorists Win

Professor Chaos - The Ultimate Terrorist

Professor Chaos - The Ultimate Terrorist

Recently the call went out for SQL Bloggers to submit their best work for the PASS Log Reader Awards. A few of the best SQL Bloggers got together and created this contest to inspire and encourage others to create top quality content.

I was encouraged to enter my Encyclopedia Brown Mystery posts I created and I did so. It’s not strictly SQL, so I entered it into the “Unusual Blog Post” category. I’ve always been seen as unusual anyway, so I figured I had a good shot to win.

The entry even got some good buzz around it on Twitter when SQL Chicken BlogTwitter raved about it.


So, I waited with baited breath for the results. I worked on an acceptance speech. Well, the winners were announced and ::heavy sigh::

I didn’t win.

When I read the post and didn’t see my name, I started to comment on Brent’s post, “I’m real happy for ya, and I’mma let you finish but the Encyclopedia Brown Post was one of the most unusual of all time!!!”

However, In an effort to be classy and take my defeat well, I refrained from doing that. After brooding about being unappreciated and misunderstood for several days, I started feeling emo, wearing goth clothing, and listening to sad music to make my point to the community that nobody understands me.. However, I realized that wouldn’t get me noticed either as one of the Award Judges already tried that.

After careful consideration, it all comes down to SQL Chicken’s comment: The Terrorists Win. (Scroll Down)


Vote for me next time or the bird gets it!!!


Seriously though, congrats to the winners. 🙂

“Anonymous” Surveys

One of my friends was given this electronic survey at work. Read it and see if you come to the same conclusion that I did.

Although the survey is anonymous, we would like some information about you so that we can analyze the results.

All questions marked with an asterix are mandatory.

Please select your part of the business Division*

  • List of four Divisions of the Company
  • Strategic Business Unit*

  • Short list of possible Business Units per Division Chosen above. This indicates the small sub company for which he works.
  • Where do you work?*

  • At the main location
  • At a different company facility
  • At a customer facility
  • Another location
  • Choose the description closest to your function or profession*

  • List of very specific job functions particular to this organization.
  • Choose your level*

  • Executive
  • Senior Manager
  • Manager
  • Supervisor
  • Lead
  • Employee
  • Choose your length of service*

  • Less than 1 year
  • 1 year to 3 years
  • 4 years to 10 years
  • 11 years to 15 years
  • 16 years to 20 years
  • Over 20 years
  • Choose the number of hours you work in the average week

  • Less than 10 hours
  • 10 hours to 20 hours
  • 21 hours to 30 hours
  • 31 hours to 40 hours
  • 41 hours to 50 hours
  • 51 hours to 60 hours
  • 60 hours
  • Please select your gender

  • Male
  • Female
  • Not Disclosed
  • Please indicate your age from one of the ranges

  • Under 20
  • 20 to 30
  • 31 to 40
  • 41 to 50
  • 51 to 60
  • 61 to 70
  • 71 and over
  • Not Disclosed
  • Then follows a list of very specific probing questions regarding his/her feelings about the job, management, decisions, ethics, etc.

    Considering this is submitted via a website accessed from work, they have his IP as well. Does this company really think that if the employee is disgruntled that he/she would say so? My four year old nephew could deduce his identity from the list of questions.

    My friend completed the survey indicating that everything was just ducky with the company.

    M2M Can’t Combine Shippers and Sales Orders on Invoices. Why Not?

    Please be sure to read the comments as this post is actually in error. Thanks again Andy. 🙂

    Did you know that you cannot invoice from a shipper and a sales order on the same invoice? I was mildly surprised myself when I realized it. Let me take a moment to explain why this is a problem.

    Imagine creating a Sales Report to determine the net value of everything not shipped nor invoiced that it is in the Open state. The value of sales orders is actually determined in the Sales Order Release (SORELS) table. The method for doing so is this:

    ((SORELS.forderqty - (SORELS.fshipbook + SORELS.fshipbuy + SORELS.fshipmake) - SORELS.finvqty) * SORELS.funetprice

    In other words, the order quantity minus the quantity shipped and invoiced, times the net value per piece. Makes sense, right?

    Problems arise when you have sales orders that contain both shippable and non-shippable items. For example, we have a sales order for Acme, line 1 is a new radar device for one of their Rockets, and line 2 is the R&D cost for adding Roadrunner detection. Line 3 is a different long-term product, which keeps the sales order open after lines 1 and 2 are finished. Therefore, lines 1 and 2 have a quantity of 1 for SORELS.forderqty.

    Anyway, your company ships the radar to Acme, and your accounting department invoices them from the shipper. When shipped, M2M updates the SORELS.fshipmake for the radar to the value of 1, and since 1-1=0, that value is removed from your report.

    However, since accounting cannot invoice from the sales order on the same invoice, they must do a miscellaneous line item instead. This is transparent to the customer, but it causes problems in the SORELS. Line 2 is never updated to show that the SORELS.finvqty is now 1, and that line item still shows outstanding. The net amount is off by that amount in your report.

    Made2Manage’s response is that we should invoice sales order line 1 and 2 separately, but some customers will refuse to pay multiple invoices like that. Also, you could ship the R&D fee from line 2, but it would show up on the customer’s packing list and that is confusing as well.

    Has anyone else experienced this? How do you handle it?

    M2M Warning. Please Read ASAP

    This morning approximately 75% of my users were no longer able to open M2M without an error. The error says “Omsg is not found” and this typically indicates a VBA issue.

    The problem appears to be related to a set of updates issued yesterday and is only affecting those machines which have Office 2007 installed. I am running Windows XP and M2M Version 5.6.

    I will follow up with details after I have thoroughly tested everything, but I wanted to give a heads up to any of my readers in case they run into the same problem.