|
|
 Listen folks, I have been a computer geek a long time. I started with the early versions of DOS and have been using Microsoft operating systems for nearly 20 years. I used to know how to configure extended memory, expanded memory, etc in order to get various PC games to work in less than 1mb of memory. I still have a copy of Windows 3.1 and keep it as a memento along with my high school yearbooks and Star Wars figures. Yes as I type this I realize how geeky that sounds.
When in college, everyone tried to get me to switch to a Mac, I stuck with Windows. When I taught high school, I was once again enticed by the lure of Macs, and I persevered. For years, I had a running argument with my uncle every Christmas about PC vs. Mac. My opinion was always that Macs were for people who weren’t capable of using a PC. They were expensive, proprietary, and had less software available for them. Also, since I was an avid gamer, I remained with Windows because gaming on a Mac is laughable. I’ve argued on your behalf so many times I’ve lost count.
Them: PC’s get infected with viruses and spyware all the time.
Me: I patiently explain that since Windows is ubiquitous, they are the OS that is targeted by hackers. Suck it up and install anti-virus software.
Them: Windows crashes all the time.
Me: Mac has it easier because their OS only has to work on their hardware. Windows has to work with a myriad of different manufacturer’s products.
Them: PC’s are too complicated; Mac’s just simply work.
Me: Plug and play, USB, etc has made PC’s easier. You simply have to know what you should and should not do with a computer, like a car.
Windows XP’s success felt like some vindication of my fanboy devotion. It wasn’t a great OS, but it was better than Windows 98 and the abortion that was Windows Me. I buy your other products as well. I was an early purchaser of the original Xbox and have an Xbox 360. I use your peripherals – mice, keyboards, etc.
I am a PC, I have always been a PC, and I make my living with PC’s. I could have made one of those commercials for you. Until now, I had espoused that Mac users were elitist and lazy. However, this has finally changed.
 I had high hopes for Vista, but that turned out to be abysmal. My company wisely skipped Vista entirely in its software progression and will eventually move from XP to Windows 7 for this reason. I bought a high end Vista 64 machine for home use and have had so many driver and software incompatibility issues that I cannot imagine a non-geek owning one successfully. You (Microsoft) know there are millions of people remaining on Windows XP because Vista sucks. The fact that you won’t allow users to upgrade from XP to Windows 7 is further evidence of your hubris, and this attitude may eventually be your undoing.
 Also, I should mention at this point that while I do own Xbox 360, I have been through 5 of them. All of them died with the Red Ring of Death, over and over again. Being a fanboy, I dutifully replaced them under warranty, even when some of the replacements lasted less than a month. I have actually purchased 3 in addition to those replaced under warranty. I purchased one of them just a couple of months ago for my nephew for Christmas because his Xbox was replaced three times and finally died out of warranty. For years you denied there were problems with that product too, until the news coverage became overwhelming. Even through this, I continued to use your products and said little.
However, I am sick of this. I have made excuses, I have turned a blind eye, but I’m sick of it. You are losing to Apple. Until this point, you have plodded along; content to be second best and I have run out of excuses. Your dominance in the market has made you complacent and lazy. Commercials sway public opinion and they are killing you with them. Their commercials are funny, compelling, but worst of all – True. Your commercials, which as I said before sway public opinion, are inane at best and insulting at worst. What on earth were you thinking with those Jerry Seinfeld Commercials? They were pointless, but at least they were funny. You followed up with, “Windows 7 was my idea.” Do you really expect me to think that Windows 7’s improvements were actually your user’s ideas? Don’t you have legions of top notch engineers who are paid to have these ideas?
Times have changed. Macs are now produced on the same hardware as PC’s, and are not much more expensive. Macs are more stable. Computer gaming? With the exception of WOW, who plays games on PC’s anymore? While there are more options in Windows software, most of the truly great software products are already offered on Mac anyway.
It is time to wake up, Microsoft. We don’t actually need your products anymore. The alternatives are becoming more compelling all the time. Doesn’t it concern you that people who make a living with your software have already jumped ship? One of my big surprises after I joined Twitter is that some of the brightest stars in the SQL Server universe no longer use your operating system. Take a look:
How do they work with SQL Server? Well, in general they use virtual machines which run on the alternative OS.
The recent problem with my new laptop simply put an exclamation point on this. Now, I don’t know whose fault the software problem was, Microsoft or Lenovo. My point is that it doesn’t matter. Perhaps it isn’t fair, but if people have problems with Dell, Lenovo, HP, and other computers; the common factor is Microsoft.
Did I write this blog simply to jab at Microsoft? No. I sincerely want to remain a PC. That’s why I bought the Windows 7 laptop. Please get it right this time. It’s not too late. Stop producing annoying, bug ridden software and other substandard products.
Oh, and a video game system that didn’t die on a monthly basis would be nice too.
I have been promising to create a series of instructional videos regarding SQL, Crystal Reports, and Made2Manage for some time. I asked the SQL Community for suggestions for an excellent Laptop Configuration to record these videos.
I went with the IBM Lenovo Thinkpad T400 with several upgrades including more RAM, faster processor, and a SSD Hard drive. It arrived without incident and it looks great, it’s relatively light, etc.
I powered it up and answered the obligatory questions about windows startup, agree to some EULAs, etc. My first problem occurred approximately 10 minutes after start up. I attempted to use a Microsoft Bluetooth Mouse with the laptop so I turned on the Bluetooth and tried to add a device.
 Fail
Don’t they test this crap? I used a current Microsoft Mouse with Windows 7 and it fails right out of the box. Doesn’t Lenovo test these images for compatibility?
Google to the Rescue
Being a computer geek, I fired up Google, searched for “AcWinHlpr.exe,” and received exactly three sites on the entire internet referencing it. They were all in Japanese. I used the Google translator and none of them had anything to do with my problem. Try it yourself.
Lenovo Support to the Rescue
 I broke down and called Lenovo and waited on hold. I gave them all of my information, and the technician told me to restore the laptop to factory defaults. Think about that a moment… I had just taken the it out of the ^&*%*% box. However, when I flat out asked him, how he had come to the conclusion; he actually admitted that “when Google fails you, what else is there to do?” He instructed me that I could hit the ThinkVantage button and follow the prompts. I thanked him and hung up.
So Lenovo has built in a plethora of software to fix this stuff. I’ll just use that to fix it. I hit the ThinkVantage button and nothing happened. That’s right; the troubleshooting button wouldn’t work either. I restarted the laptop to no effect. I attempted to trigger it from the on screen icon and that didn’t work either.
Attempting to Create Restore Media
Rather than wait on hold with Lenovo again, I opened My Computer and found the expected restore partition on the hard drive. However, it wouldn’t allow me to begin a restore from there, only make media from it. It never ceases to amaze me that you can spend a small fortune on a laptop and they no longer give you a manual, nor will they throw in 50 cents worth of restore media. I attempted to create the restore media, which has two parts: recovery and application data. One of them failed in process, but I could not tell which one. I examined the contents of the DVD which had actually recorded properly, but it is not obvious which it was. So, started over and tried to create them one at a time so I would be able to label each one. I was then presented with the following message.
 Orly?
WTF. Really? Seriously? I can only make 1 copy of an operating system that is already crippled in that it will only work with this model of Lenovo laptop? Doesn’t the operating system need to be “validated” anyway? This is how Microsoft combats piracy?
I’m sure that counterfeiters in China, Russia, and elsewhere were really counting on using a ThinkPad laptop as their source of Windows 7. This is just silly.
So now I have DVD’s, whose individual purpose is not obvious. I was afraid to remove the recovery partition to reclaim space because I was not certain I could do another factory restore with the discs I have.
The good news is the full factory restore appears to have fixed the problem, though there is no rational reason why it should have. The laptop passed its full hardware and stress tests without incident.
After waiting on hold again with Lenovo they agreed to send me restore media. However, when I attempted to actually use the laptop, the “I” key on the keyboard would not work. Some of you may have seen my tweet:
!’m !nsanely !rr!tated. My Th1nkpad sh!pped w!th an !mpa!red ! key. !’m !mpatently wa!tng !ntently for !ts keyboard to be f!xed.
To their credit Lenovo sent a new keyboard, which I then had to replace myself. The new keyboard appears to work perfectly, and if nothing else goes wrong I should be posting videos soon.
I’m not complaining how Lenovo handled the issues; I’m just frustrated that the issues needed to be “handled” to begin with.
What disappointing “out of the box” experiences have you had?
As long as I have been working with the M2M Database I had always wondered one thing. Why on earth does M2M use 1/1/1900 dates? It’s fairly obvious that it was an artificial date when one wasn’t applicable, but why? Why not just allow your date fields to be null and be done with it?
For example, when I run the following query to return the Last Payment Date (fdlpaydate):
SELECT fdlpaydate, fcustno, fcompany FROM slcdpm I receive:
 Annoying Why Should I Care About 1/1/1900?
For me, the reason was simple. My executives hate the way it clutters reports. They’d much rather display a blank field if there isn’t a valid date. In the past, I would use a “Print When” option in Visual FoxPro or something analogous in Crystal Reports. In other words, I would hide the bogus date in the presentation layer. As I migrate to SQL Server Reporting Services, I wanted a more universal method to suppress these dates so they would be suppressed no matter which presentation medium I chose.
The 1900 Date is Actually a Null?
No, not exactly. I investigated this because I needed to know how to suppress it. When one looks at the create statement of any date field in the M2M database, the same constraint is used:
[fdlpaydate] DATETIME NOT NULL “Not Null” is the constraint. It turns out that the database returns the 1900 date because the value is actually ‘’ and not null.
How do we Fight the 1900 Scourge?
Well, first before implementing any code from this site, please read my disclaimer. What I’ve chosen to do is to create a User Defined Function (UDF).
CREATE FUNCTION ExtractDate(@DirtyDate DATETIME)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @CleanDate VARCHAR(10)
SELECT @CleanDate =
CASE
WHEN @DirtyDate = '' THEN ''
ELSE CONVERT(VARCHAR(10), @DirtyDate, 101)
END
RETURN @CleanDate
ENDYou’ll notice that I check the date for the empty string ‘’ and then “filter” out the meaningless dates. The convert statement returns a string in the typical Month/Day/Year format.
How do I Use the User Defined Function?
The only trick to using a UDF is remembering to reference the owner, in this case DBO, when calling it. Therefore, you would use the user defined function like this:
SELECT DBO.ExtractDate(fdlpaydate), fcustno, fcompany FROM slcdpm  The Results of Using the UDF.
Feel free to use this (after you’ve read and understood the disclaimer). However, you have made a change to your M2M database, and you should document doing so. It’s unlikely that such a change would affect your ability to migrate upward, but you can never be too careful. Document it, and be prepared to delete it if you have problems.
Any questions or suggestions?
Yesterday I explained the basics of how FastForms deals with SQL Server. Today I’ll discuss the skills they don’t teach you in M2M classes; how to manipulate SQL directly and save your company thousands in manual data entry.
How do I automatically populate my extension table?
Continuing our hypothetical situation from yesterday, our employees at Acme Inc. do not want to have to go through all of their records to update them. This is tedious and actually impossible if the sales orders have been closed. Therefore, Acme wants the color value to be automatically entered for all sales orders already in the system. How does one accomplish this? I thought you’d never ask.
If you take a look at the create table statement, which can be obtained by right clicking on the table and choosing Script Table as, Create To, New Query Editor Window; you will see the fields that were created.
CREATE TABLE [dbo].[SORELS_EXT](
[Identity_Column] [INT] IDENTITY(1,1) NOT NULL,
[Timestamp_Column] [TIMESTAMP] NOT NULL,
[FKey_ID] [INT] NOT NULL,
[COLOR] [CHAR](10) NULL, Our table has 4 fields. The Identity_Column and Timestamp_Column are automatically assigned. The FKey_ID is a foreign key to identity column of the parent table, and the Color field is obviously our data. Knowing all of this, how do we insert the correct data?
Crafting a SQL Insert Statement Step by Step
For simplicity’s sake, we’ll create the insert statement automatically in the same way we generated the create statement. This step may seem trivial, but this saves time when dealing with a large table.
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
VALUES
(<FKey_ID, INT,>
,<COLOR, CHAR(10)>)
GOWhy aren’t Identity_Column or Timestamp_Column listed? As I mentioned before, Identity_column is an auto-number column and will generate an error if you attempt to manually assign it without using Set Identity_Insert On. We don’t’ need to bother with that. Timestamp_Column is also auto generated as well.
Therefore, we could insert one record at a time manually like this:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
VALUES
('12345'
,'Blue')
GOMake sure you delete the inserted records between insert attempts. The next step is to perform an easy insert from a select statement such as:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
SELECT Identity_Column,
'Blue' FROM sorels
GOThis inserts a record into the extension table for every record in the parent and inserts a color as well. Verify that it worked and delete the records. Next, flesh out your select statement like so:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT](
[FKey_ID], [COLOR])
SELECT Identity_Column, CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
FROM SORELS SOR
GO The code creates all of the records in your extension table and the correct color.
What if I need to add fields to an already existing extension table? What is an “upsert”?
What if you have an existing extension table where some of the records already exist? Let’s say that Acme already had a field in the extension table called FuseLength. Some of the parts already have a value for FuseLength and some don’t. Well, that is a little more complicated. If you attempt to insert a record for color that already exists (has a value for FuseLength), you will receive an error similar to this:
Error Date and Time Removed SQL Server Database Error: Violation of PRIMARY KEY constraint ‘PK_SORELS_EXT’. Cannot insert duplicate key in object ‘dbo.SORELS_EXT’. 4 0
The script should update records that already exist and insert the others. It’s important to note that all FastForms extension fields are nullable, meaning that a value is not required. If you are using SQL 2000 or 2005, the simplest way to perform this is with two separate statements. The combination of them is often referred to as an “upsert.”
The first statement updates the existing records with the correct color:
UPDATE SORe
SET COLOR =
CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
FROM M2MDATA01.dbo.SORELS SOR
INNER JOIN
M2MDATA01.dbo.SORELS_EXT SORe
ON SOR.IDENTITY_COLUMN = SORe.FKEY_IDThen follows insert statement, which needs to check if an extension record already exists:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID],
[COLOR])
SELECT identity_column,
CASE
WHEN LEFT(sor.fpartno,2) = 'BL'
THEN 'Blue'
WHEN LEFT(sor.fpartno,2) = 'RD'
THEN 'Red'
ELSE 'White'
END
FROM m2mdata01.dbo.sorels sor
WHERE sor.identity_column NOT IN (SELECT fkey_id
FROM sorels_ext)
GOThis effectively inserts all missing records into the extension table. The preceding scripts work in all SQL versions beyond 2000.
How Can I “Upsert” with SQL 2008? What is the Merge statement?
I’m glad you asked. With SQL 2008 you may want to use the Merge statement.
MERGE Sorels_ext AS SORe
USING (SELECT SOR.identity_column
, CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
AS colors
FROM Sorels AS SOR) SOR1
ON (SORe.fkey_id = SOR1.identity_column)
WHEN MATCHED
THEN
UPDATE SET SORe.fkey_id = SOR1.identity_column, SORe.Color = SOR1.colors
WHEN NOT MATCHED
THEN
INSERT (
fkey_id, Color)
VALUES (
SOR1.identity_column, SOR1.colors);I know it looks complex, but once you understand the syntax, the Merge statement can be easier to write as well as more efficient for the server. The target table (table being acted upon) follows the MERGE statement, in our case Sorels_Ext. The source of the data follows the USING statement in our case Sorels. Notice that I aliased the CASE which determines color with the word colors to make things simpler to read. The ON clause specifies the link between the tables. The WHEN MATCHED statement specifies the action to occur if a record is found in both tables, and WHEN NOT MATCHED is executed when a record is missing from the target table.
This is executed as one statement and should theoretically be faster since the tables are only touched once.
In summary, in these two articles we’ve created a FastForms customization and automatically populated the fields in it using Insert, Update, and Merge.
Any questions or suggestions?
In this article I’ll focus on the T-SQL component of a successful FastForms customization involving extension tables. I tend to demonstrate things in very small steps, and often work that way as well. This would be a good time to repeat 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.
 Acme Product Testing FastForm Field Creation Basics
In our example, we work for Acme Inc. which sells rockets. As the Admin, Acme has tasked you to create a method to track which color rockets are ordered. In the past Acme accomplished this with separate part numbers for each color, and each of their rocket part numbers begin with a color code. Acme wants to separate the color component from their part numbers.
I’m not going to discuss the basics as to how to create FastForms screen customizations. Made2Manage offers a downloadable class for that. For the sake of brevity, lets assume that the Admin has already created a combo box for color on the Sales Order Release Screen(SORELS) and populated it with the appropriate values. To create the FastForms extension table, click on the Data Tab of the FastForms Tool Box and expand SORELS as seen on Figure 1 below. Right click Extension Fields and select Add Field.
 Figure 1
Name your new field in the Field Properties and select a Character type of length 10 (figure 2 below). I chose 10 because most color names require less than 10 letters.
 Figure 2
M2M will create the table and field in the SQL database and give you the warning below. This is helpful, but it would be more helpful if the warning didn’t trigger every single time you added a field. After you create the field, assign it to the control under the Properties Tab.
 Figure 3 How do I link my extension table into reports?
The process is fairly simple once you understand how the extension tables work. In this case, a SORELS_EXT table was created and that table links to the SORELS table via the FKey_ID field. M2M also creates a stored procedure which handles extension table activities such as inserts and updates. The link for reporting would look like this:
SELECT * FROM SORELS SOR
LEFT OUTER JOIN SORELS_EXT SORe
ON SOR.IDENTITY_COLUMN = SORe.FKEY_IDIncidentally, I almost never use an inner join to an extension table for reporting purposes. If you do so, you will remove any records from your query that have no corresponding values in the extension table. I know that seems obvious, but I’ve helped many Admins who made that mistake and gotten erroneous report data back.
Tomorrow I’ll cover how to automatically populate your new extension table.
 I originally wrote this article some time ago, and then shelved it because I found that Jason Massie had already used the title, though his message was entirely different from mine. Since this is an article about plagiarism, I thought it ironic to plagiarize his title. However, with the recent uproar about plagiarism on SQL blogs, I am going ahead anyway.
If you, dear reader, are not aware of the problem, I’ll bring you up to speed with some links to the blog articles. The posts which originally caused me to write this were from Brent Ozar and Lee Everest, which lamented the problems of content theft and condemned those who engage in it. Brent’s article is excellent in that it informs the reader how to fight content theft.
Yesterday there was a flurry of Twitter and blog activity from the SQL Community regarding blog plagiarism. Todd McDermid, Denny Cherry, Jorge Segarra, and again Brent Ozar discussed blog plagiarism problems. Brent is even offering a bounty on people who report plagiarism of his work.
All of these guys are respected bloggers and DBA’s and I’m not going to argue with, nor attempt to disparage any of them. We all agree that content theft is theft and should be dealt with. However, my perspective is a little different.
If you will, picture me as a waiter in some swanky Hollywood restaurant. I’m going to acting classes and auditions at night (analogous to the Saturday SQL classes I’m regularly taking), trying to get my big break. When these three guys walk in there’s a flurry of activity and are of course given a VIP table. They’re talking about their films, trysts with their buxom costars, and Academy Awards they’ve won. As I listen to this while serving them, I am filled with envy and a renewed commitment to better myself.
During the main course they start complaining because the paparazzi are constantly following them around, stealing information and images from them, and lamenting all the work they have to do to keep this from happening. They complain about taxes on the millions they are making in film. Perhaps they’ve hired bodyguards, agents, accountants, etc all to protect their property both real and intellectual. All of this is such a hassle.
I am that waiter. I hope someday to be that well read. I hope to produce a blog worthy of content theft.
I guess my message is that you should be happy that you have to worry about it. You’ve made it. You’ve earned the right to chase these guys down who are stealing your content, because it’s worth stealing. I envy you, and you guys inspire me to study, blog, and raise my game in general. I only wish to give you some perspective.
Now, dear reader, please forgive me as I go back to waiting on this table. “Can I get you another bottle of Zima, Mr. Ozar?” 
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.
Snip
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?
 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.
YOU KNOW AMERICAN WORKERS ARE IN BAD SHAPE WHEN A LOW-PAYING, NO-BENEFITS JOB IS CONSIDERED A SWEET DEAL. THEIR SITUATION ISN’T LIKELY TO IMPROVE SOON; SOME ECONOMISTS PREDICT IT WILL BE YEARS, NOT MONTHS, BEFORE EMPLOYEES REGAIN ANY SEMBLANCE OF BARGAINING POWER. THAT’S BECAUSE THIS RECESSION’S UNUSUAL FEROCITY HAS ACCELERATED TRENDS—INCLUDING OFFSHORING, AUTOMATION, THE DECLINE OF LABOR UNIONS’ INFLUENCE, NEW MANAGEMENT TECHNIQUES, AND REGULATORY CHANGES—THAT ALREADY HAD BEEN ERODING WORKERS’ ECONOMIC STANDING.
DIMINISHING JOB SECURITY IS ALSO WIDENING THE GAP BETWEEN THE HIGHEST- AND LOWEST-PAID WORKERS. AT THE TOP, PEOPLE WITH SOUGHT-AFTER SKILLS CAN EARN MORE BY JUMPING FROM ASSIGNMENT TO ASSIGNMENT THAN THEY CAN BY STICKING WITH ONE COMPANY. BUT FOR THE LEAST EDUCATED, WHO HAVE NO SPECIAL SKILLS TO SELL, THE NEW DEAL FOR LABOR OFFERS NOTHING BUT DOWNSIDE.
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.
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.
 This is what should happen. However, they can delete a fiscal year in the following three steps:
- Click Modify on the Accounting Years screen.
- Change the Thru Date and From Date to next year’s date.
- 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):
DELETE FROM glrule WHERE IDENTITY_COLUMN=@P1 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.
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?
USE [M2MDATA01];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE LogDeletes (@LogInfo VARCHAR(256))
AS
BEGIN
DECLARE @RecCount INT, @MIN INT, @MAX INT, @Target INT
SET @RecCount = (SELECT COUNT(*) FROM M2MDeleteLog)
IF @RecCount > 20000
BEGIN
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
END
INSERT INTO M2MDeleteLog (UserName, UserSPID, LogDate, Workstation, LogInfo)
VALUES (SYSTEM_USER, @@SPID, GETDATE(), HOST_NAME(), @LogInfo)
END
GO 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?
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:
- 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.
- Open SQL Enterprise Manager and locate the database you wish to delete. Right click on it and select delete.
- If you have backups for the practice company you must delete them as well. You can locate them through Windows Explorer.
- 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.
Pack
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.
- 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.
Pack
CLOSE All
- 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?
Page 5 of 20« First...«34567»...Last »
|
|
Popular Articles