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.

    PASS. Somewhere Out There..


    Fievel - An American Tail

    All of the Tweeting about PASS prompted me to write this. Sung to the tune of “Somewhere Out There.”

    Somewhere out there…
    At the PASS Summit…
    People are learning SQL and Tweeting about it.

    Somewhere out there…
    Ozar, Fritchey, and Delaney…
    are teaching Clouds, Wait Stats, and High Availability.

    And even though I know it’s about learning T-SQL and SQL Server too,
    I’m also miffed that I’ll also miss the next tattoo.

    And even though I want to learn clustering, indexes, and Integration Services….
    I also want to count the number of “it depends.”

    Somewhere out there..
    I’ll continue to train,
    Then next year, I’ll be out there..
    Where best and brightest shine.

    Original Lyrics:

    Fv: Somewhere out there,
    beneath the pale moonlight,
    someone’s thinking of me and loving me tonight.

    Ty: Somewhere out there,
    someone’s saying a prayer,
    that we’ll find one another in that big somewhere out there.

    (*)Ty: And even though I know how very far apart we are,
    it helps to think we might be wishing on the same bright star.

    Fv: And when the night wind starts to sing a lonesome lullaby,
    it helps to think we’re sleeping underneath the same big sky.

    Together: Somewhere out there,
    if love can see us through,
    then we’ll be together, somewhere out there,
    out where dreams come true.

    Character key:
    Fv = Fievel, Ty = Tanya, Tg = Tiger.

    Search Meter Surprises

    I’ve been making changes to this blog lately including a new theme, plug-ins, and tons of new content. One of my unofficial mentors, Brent Ozar, who was recently made a Microsoft MVP, has been on a tear as of late. This has inspired me to take my blog to the next level as well.

    Anyway, one of the new plug-ins I implemented is Search Meter for WordPress. This has been enlightening because I can now track what people are actually looking for. One obvious search that repeatedly showed up was something like, “Subscribe New Posts.” I had not noticed that the Subscribe plug-in had not carried over with the new theme. Oops.

    In addition, people are searching for T-SQL terms, M2M screen and report names, as well as Crystal Reports issues.

    However, the most entertaining revelation is that some M2M people search the blog for their names. I already knew that many M2M employees read the blog, but I had no idea they would search for mentions of their names. Let me just reassure the folks at Consona that I won’t call anyone out by name, especially in a negative light.

    What topics would you like me to cover or discuss in more detail?

    Running Twice as Fast

    This past Saturday I attended the first of four days of SQL Server Development Training. The instructor, Mike Hotek literally wrote the book on SQL 2008 Database Development. A fellow M2M Administrator asked me why I bothered because we are not yet on SQL 2008 and I thought it would make a good blog post. The question reminds me of Alice in Wonderland.

    “A slow sort of country!” said the Queen. “Now, here, you see, it takes all the running you can do, to keep in the same place.
    If you want to get somewhere else, you must run at least twice as fast as that!”

    Listen, unless you intend to administer Made2Manage the rest of your life, you’d better learn to enjoy learning. While the pace of M2M development has been fairly sedate, the rest of the technological world moves at a blistering pace. If you are putting in your time simply doing a good job, you are not only stagnating, but falling behind. I am studying SQL 2008 Business Intelligence and Database Development because my career is rapidly moving in that direction. I hope to be MCTS certified by the end of the year and MCITP next year.

    Why am I telling you this? Well, one reason is this makes my goal public and motivates me to work harder toward it. Also, I want to encourage my readers to avail themselves of every opportunity to educate themselves and further their careers. If necessary, do it in your free time and using your own money; I am. Don’t just keep pace, move ahead.

    Oh, and incidentally I finally understand page splitting, clustered vs non-clustered indexes, and B-trees. Luckily Mr. Hotek was patient when I asked a bunch of questions.

    What are you doing to further your career?

    Suggestions for Prospective Vendors

    First, let me get this out of the way, I have great respect for sales people. It’s a dirty job and someone has to do it; it certainly won’t be me. My father was a salesperson of one type or another his entire life.

    I can only imagine how difficult this job is considering the current economy. The stress of being commission based must be severe, which motivates many salespeople to stray from the accepted paths. I always try to be courteous and understanding with salespeople because I know what a rotten job it can be.

    However, I would like to send a message to prospective vendors regarding some tactics, which simply do not work or are blatantly irritating and offensive. This post is somewhat similar to my suggestions for vendor support.

    Cold calling is dead. This is especially true if there is no connection or specific reason for the call. If I filled out a card at a seminar asking for information, please call. If our connection is because my business happens to use computers, forget it. If you sell a M2M related product, I may give you a few minutes. Nobody likes cold calling, especially you (the salesperson). In college, I worked as a market research phone representative asking boring questions about products and such. I hated my life. Near constant negative feedback was so depressing. Stop cold calling, it doesn’t really work.

    Spam is evil, even if I already buy from you. If you have some revolutionary new product, send me an e-mail. However, if I spammed every week with some past product that you dusted off to try to re-sell me, I get annoyed. I didn’t buy it two years ago, and presumably I still know about it, so why would I want to buy it now? This is especially irritating when current vendors send the e-mails directly from the salesperson’s account. I want my account manager’s e-mails to come through in case something important happens with my account, but not the spam. I cannot block the account without losing all of the e-mails.

    Bogus Twitter Followers are annoying as well. I will not elaborate much on this because Brent Ozar already covers it.

    Your internet presence is troubling. If I am interested in doing business with you, you can bet that I will Google your company. I suspect nothing less about prospective future employers. If they Google “David Stein SQL” and they come across a bunch of negative comments, I will not get the job. Customer service is particularly important in this age of easy information.

    Unsolicited appointments are a scourge of marketing. It works like this. A Salesperson gets my e-mail address and sends me an Outlook Meeting Request. Rather than actually asking for a meeting, I am scheduled for it. Often I have never even spoken with them. In fact, the meeting requests usually don’t ask for a meeting either, they simply say something like, “If this time isn’t convenient, when would you like to reschedule?” Umm… How about never? I will never do business with you. How do I handle these situations? I simply leave the appointment on my calendar, and then smile when I get a follow up e-mail complaining about missing the appointment. If I waste enough of their time slots, perhaps they will stop bothering me.

    Concealed sales pitches are common as well. Do not try to entice me with offers of free training, and then waste my time with a sales pitch. I don’t respond well to being tricked and will refuse to do business with you. Certain vendors have a notorious reputation for offering little actual value in local and national user group meetings because they are thinly veiled sales pitches. This is particularly odious when my company actually pays for the event. My company does not want to pay several thousand dollars to send me to Vegas simply to listen to sales pitches for new ERP modules or reporting packages. If you want my time (or for that matter my money) you had better provide actual content. One vendor who does this well is Quest software. Many of their products augment and correct shortcomings of the standard Microsoft products. Their Pain of the Week online presentations are particularly good. They will discuss a problem with SQL Server, and how to fix it with the native tools. However, if their product is easier or more efficient, they will demo that as well. Yes, you often get a sales pitch, but it is typically reserved for the end, after you have received valuable information. Incidentally, I do not have any affiliation with Quest, other then using TOAD.

    Be professional in all of our communications. Just because we both have experience in the same ERP system, we are not instantly bosom buddies. Salespeople who over extend themselves to be your instant friend give me a slimy feeling. Also, check your messages to me for spelling and grammar errors before sending them. I received the following message recently, which demonstrates another common problem.

    Hello {!Contact:First Name;},

    My firm is a Business Objects/SAP Partner. They have asked us to follow up with you to see if you require any additional assistance after your download of the {!Contact:Description;} product. I am available to help you and your company in the following capacities:

    • Product Purchase Guidance
    • Technical Training
    • Consulting Services

    I’d like to give you a quick call to introduce myself so you feel comfortable contacting me in the future. What is the best number and time to call?

    I realize we all make mistakes, but do you think I’m going to do business with a technical company that doesn’t know how to mail merge?

    Practice your product demos and look polished during your presentation. I know that accidents happen, laptops die, windows errors pop up, etc. However, if you want to sell your product, know how to run the product. Earlier this year I met with a potential vendor with a new reporting product that did not go well at all. The two man team (sales and technical) managed to lock themselves out of their own product in the first 10 minutes and then wasted the better part of an hour trying to fix it. After giving up, they spent the remaining ten minutes discussing what the product SHOULD be able to do with screen shots. Do you think I bought this product?

    Focus on your product, not the competition. Don’t spend your time with me focusing on negative themes. I know that sounds ironic since most of this post is about negative actions of salespeople. Don’t spend too much time tearing down their product, spend it building your product up.

    Get the hint if I don’t respond. This theme relates to most the previous points. My company may not be interested in your product for several reasons. Perhaps it is a budget issue. ERP Software in particular is so expensive that switching to another one can be ridiculously expensive. We may already have a business partner and we are happy. Badgering me with sales pitches is not a productive use of your time or mine.

    What do you think?

    Report Guidelines: Report Approval and Deployment

    This article is day five in a week of reporting articles.

    Create and follow standard procedures for report deployment. The actual procedure depends greatly on the report method. Since I often use Crystal Reports Server and SQL Reporting Services (SSRS) for my report presentation, I have a Testing folder where all finished reports go prior to manager approval. Specify who can approve such reports.

    In my experience, users have the tendency to request reports as single requests for data. In other words Milton will ask you, “Can I have the last 3 years of invoiced sales for Acme Rockets that shipped to Arizona?” There will be a temptation to fulfill his request as expediently as possible as an ad hoc query. If possible, put in a little extra effort and create a stand-alone report that can be re-used, regardless of whether the user wants it. You may even ask Milton if he will ever need similar information again and he will likely say no. However, next week he will come to you, most likely holding his red stapler and ask, “Now Bill needs year to date invoiced sales for Initech.” To most users these are completely different reports. Think ahead and create more versatile, reusable reports. This will save time for both you and Milton.

    Create a watermark (if possible) to differentiate which reports have been approved and which are still being tested. Watermarks are typically semi-transparent logos or large text such as “Draft” shown behind the data. Watermarks “encourage” users not to use a report ad infinitum, which has yet to be approved because their data can be hard to read. Some may balk at this, but trust me if you do not create and follow a similar procedure, reports will become disorganized and never are certified. I’ll follow up with a “How To” article to achieve this with Crystal and SSRS.

    Remember, you are (or at least this is directed to) the technical person. At a previous employer, a V.P. affectionately referred to me as the “chip head.” At least I think it was affectionately. You are most likely NOT the decision maker, you write reports for them. It is very unlikely that you know their data and what it means better than they do. Therefore, they must certify that your reports are right.

    It goes without saying, but use descriptive and specific report names. Reports are not helpful if the user cannot determine their purpose at a glance. To go along with that, thoroughly document every report. Inevitably, a user will come to you and ask something like, “Does this include part revisions that are….” If you do not document reports up front, you will have to edit the report to find out. When documenting, I always imagine that today may be my last day at that particular job. This is the “What happens if Dave gets hit by a bus?” kind of thinking. If someone needs to take over for you, can he/she do so easily? If not, you are not doing your job.

    Report Documentation for Crystal Reports

    Report Documentation for Crystal Reports

    In Crystal, I document using the Document Properties Window at File – Summary Info. The Comments text is displayed next to the report title on Crystal Server and assists the user in selecting their reports.

    So, this was my week of general reporting tips and guidelines. I will follow up soon with a series of videos, which will demonstrate the best ways to extract SQL from M2M reports as well as creating Crystal, and SSRS Reports based on M2M data.

    Report Guidelines: Exporting Reports

    This article is day four in a week of reporting articles.

    Most users want to export reports to Excel so they can manipulate the data. If possible, set up the report for the cleanest exporting possible. This process starts when writing T-SQL. For example, one can extract only the date instead of a datetime value when time is not relevant.

    As I mentioned in a previous post, if you are creating a VFP report for M2M only the fields called out in your prg file will export to Excel. Therefore, do not pull them in via the data environment or your export will be missing items.

    SQL Server Reporting Services and Crystal Reports will export many formats including PDF, Excel, Word, HTML web pages, and several text based formats. Excel, in my experience is the most common export format followed by PDF. In Crystal, there are two basic options for exporting to Excel, standard and data only. Most often I use standard, but if default options are used the results will often be lacking. The problem is that Crystal will merge columns and rows in illogical ways and this makes data analysis impossible.

    The answer is to select a different column width option. Depending on your report, you’ll most often use a Group Header, Footer, or the Details section to determine your column widths.

    Set the Export Properties.

    Set the Export Properties.

    Keep in mind that your users will not remember to change this setting when exporting, so there is an option to save these settings with your report. Choose File –
    Export – Report Export Options to change these options. These options are saved with the report and it will default to those settings for the users.

    I always create these settings even if the user says they will never export it. Invariably, someone will come to me in the future and complain that the export doesn’t work.

    As an aside, I have talked in the past about Excel Hell and often that hell is caused by spreadsheet exports from M2M or other ERP systems. For example, some companies want to keep track of their Sales Order Backlog or Inventory over time, but these figures are a snapshot. Therefore, an employee is assigned the task of running the corresponding report, exporting it to Excel, and keeping track of it on the network. This behavior becomes so ingrained in some users that they have difficulty abandoning it.

    For example, I posted code to facilitate Cycle Counting. The code automatically exports cycle count information to a separate table to be kept forever. After I had demonstrated the capability for the user who requested the project, he actually said to me, “Great, so I can export this to excel and….”

    Oh, how they love to export. Tomorrow I will discuss deployment.