|
|
As I was preparing to post another script, this time with an update statement, I started to insert my standard disclaimer. However, as more and more of my posts will include SQL code, I thought I would just dedicate one post for the disclaimer and then link to it. So, without further adieu…
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 taken 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.
That isn’t to say that I don’t want to help you, I help people all the time. Just be careful when you apply any code to your server or database. I do my best to research everything I post, but ultimately you are responsible for your own database.
Keep that in mind when you run code from my site or any site you find on the internet because….
One of my crappy college jobs was in market research. Yes, I was one of those annoying guys in the mall holding a clipboard. I would approach you, politely ask you about your soda preferences (for the enlightened folks we call it Pop), and then take you back into the “store” to do a taste test on several of them. Kind of like the Pepsi Challenge.
It required a few minutes, but it was general painless and we’d send you away with samples of free products. Well, I don’t want to know whether you prefer Coke or Pepsi, but I have what I think are some amazing ideas for reporting on M2M. I need your assistance to determine whether people can/will actually use them. So, in the same vein, please assist me by answering some questions.
How large is your largest M2M database? To determine the database size, simply open Enterprise Manager, navigate to your database, right click on it, and choose Properties. You should see the size of the database on the first page shown (General).
 Loading ... Is disk space an issue for you? If I told you I could simply your reporting life by automating most of the reporting process, but it would involve duplicating at least parts of your database, would this cause you an issue? Keep in mind that this process could double or triple your disk usage.
Loading ... Please paste the following script into Query Analyzer or SQL Server Management Studio. It will report the size of many of the primary tables in your M2M Database. Please respond by copying and pasting the grid into the comments area, like people did with the Orphaned Records Game.
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
and [name] in
('slcdpm',
'somast',
'soitem',
'sorels',
'qtmast',
'qtitem',
'inmast',
'inonhd',
'invend',
'armast',
'aritem',
'apmast',
'apitem',
'jomast',
'joitem',
'pomast',
'poitem',
'lamast',
'ladetail',
'shmast',
'shitem',
'glmast',
'glitem',
'rcmast',
'rcitem')
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT tablename as [Table],datasize as [Size]
FROM #TempTable
order by tablename
--Final cleanup!
DROP TABLE #TempTableThank you for participating, and stay tuned for updates on my new reporting ideas.
My girlfriend really loves the Amazing Race Show, which of course means that I have to “love” it too. It’s a reality TV show where teams compete in contests all over the world with a one million dollar prize at the end. Anyway, in one episode of last season the contestants were in Dubai. The challenge was to weigh out the exactly $500,000 worth of gold and the value was constantly fluctuating. They were given the value of gold per ounce. The trick was to realize that 16 ounces are in a pound and for the sake of this example the price of gold is $1089.87 per ounce. If you divide $500,000 by the price per ounce you can figure out how many ounces you need. Then divide that number by 16 to determine how many pounds and ounces for the final weight. Simple right? Well, remember that the price of gold was changing every minute or so. Think you can do it in under a minute?
Go ahead and grab a piece of paper and try. I’ll wait.
::Humming the Jeopardy Theme::
What? You can’t do it? Everyone learns how to do it, it’s just simply long division.
 You’ve forgotten how to do long division with decimals? I realized the same thing as my girlfriend and I were laughing at the contestants. I have forgotten how to do long division. This is ironic because I used to teach math. I am a SQL Programmer, which obviously involves math. I’m embarrassed to admit that I actually Googled how to do long division with decimals.
I can almost hear you saying, “Well stupid, use a calculator or the computer you are working on.” Well, some of the teams had calculators and got the answer. The teams that didn’t couldn’t keep up with the constantly changing price of gold and had to do an alternate challenge.
What is the point?
Why do we use calculators and computers? It’s simple, they are fast. What is the likelihood that a million dollars will be on the line for a long division problem? What is the likelihood that I will be without some kind of gadget that calculates?
In this case, it is a no-brainer. I may never have to do long division again. However, other technologies are not so clear-cut. Since moving to Dallas nearly two years ago I have used a Garmin Nuvi GPS and I love those things. I type in the address I’m looking for and it gives me turn by turn directions. It helps me find stores, restaurants, and I only use a small subset of the features of this device.
Here’s the problem. Since I use this thing everywhere I go, I have virtually no understanding of the geography here. Remember when you were a little kid and you were driven everywhere? Do you remember what it was like to start driving yourself? I actually had to “find” places that I had been to tons of times because I never had to know where they were before. In essence, this is my situation now. If someone asks me for directions to my place, I’m fairly clueless.
Can you still write cursive (besides your signature)? I really have to work at it and I hate filling out forms. Have you noticed that your spelling has degenerated since the advent of spell check? Mine has.
What the heck does this have to do with SQL?
How many of us could effectively program without intellisense? I learned Basic waaaay back in the day and really had to learn it. When discussing development tools I often hear veteran programmers laugh that you can code in notepad, but who would? We did. We learned more thoroughly too. When mistakes are painful, we make less of them. I truly think that people learning now, don’t learn as thoroughly because of intellisense.
Furthermore, as I’ve mentioned before, when I write T-SQL I do so using a program called TOAD. I really like the program and it greatly improves my productivity. However, if I open Query Analyzer or SQL Server Management Studio, I still have to Google many of the functions to get the syntax right. Is using TOAD, or another product doing myself a disservice in the long run?
What do you think?
 Total Badass We may have never saved the world with a paper clip, chewing gum, and duct tape, but many of us are MacGyvers in our daily lives. Perhaps we’re stuck with SQL Server 6 or running on ridiculously old equipment, for whatever reason we’ve had to make something out of junk.
I was reminded of this the other night. I called a friend and co-worker at my previous employer. I asked him how things were at the shop and he indicated that they were going fine, with the exception that the fax server needed to be replaced. That made me smile because I was tasked with building a fax server approximately 8 years ago with no budget whatsoever. I re-used a retired desktop computer (rather than a server), with Windows 98, with a paltry amount of RAM and hard drive space, and a Pentium II Processor. I stripped other retired computers of their dial up modems to use for faxing and filled all of the motherboard slots with them.
I purchased and installed ActiveFax, which I had heard about from a fellow M2M Admin, because it was so much cheaper than M2M’s officially supported product. At one time that “server” was e-mailing and faxing notifications of invoices, sales orders, and other documents to customers automatically. I integrated it into ordering software I wrote and Customer Service could automatically fax questions to customers as well. It handled incoming faxes as well and acted as a secondary fax machine for two departments, directing those faxes to various network printers. ActiveFax never went down. It rarely needed to be upgraded. There were no maintenance fees as lifetime maintenance and upgrades were included. It just simply worked.
Building this server was actually one of the best decisions I made during my time there. This was one of my best MacGyver moments.
I’m tagging three Microsoft MVP’s that I follow on a regular basis Brent Ozar, Tim Mitchell, and Aaron Bertrand to challenge them to share their MacGyver Moments.
I also challenge my readers, both of you, to share yours. So, what are your MacGyver Moments?
In the past couple of weeks, I have discussed the problems of orphaned records in the M2M Database and promised to provide a script to find them. So, I’ve decided to turn it into a game.
If you’d like to play, paste the following script into your Query Analyzer or SQL Server Management Studio and run it. It will return the count of orphaned records in each table. Copy and paste the results grid into the comments of the blog so we can compare. Here’s the script:
(SELECT 'Sales Order Releases' AS [Type], count(sor.IDENTITY_COLUMN)
FROM SORELS SOR
LEFT OUTER JOIN SOITEM SOI
ON SOI.FSONO = SOR.FSONO
AND SOI.FINUMBER = SOR.FINUMBER
LEFT OUTER JOIN SOMAST SOM
ON SOM.FSONO = SOR.FSONO
LEFT OUTER JOIN SLCDPM SLC
ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SOI.FSONO IS NULL
OR SOM.FSONO IS NULL
OR SLC.FCUSTNO IS NULL)
UNION
(SELECT 'Sales Order Items' AS [Type], count(SOI.IDENTITY_COLUMN)
FROM SOITEM SOI
LEFT OUTER JOIN SOMAST SOM
ON SOM.FSONO = SOI.FSONO
LEFT OUTER JOIN SLCDPM SLC
ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SOM.FSONO IS NULL
OR SLC.FCUSTNO IS NULL)
UNION
(SELECT 'Sales Order Master' AS [Type], count(SOM.IDENTITY_COLUMN)
FROM SOMAST SOM LEFT OUTER JOIN SLCDPM SLC ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SLC.FCUSTNO IS NULL)
UNION
(SELECT 'Quote Items' AS [Type], count(qtitem.IDENTITY_COLUMN)
FROM qtitem
LEFT OUTER JOIN QTMAST
ON qtmast.fquoteno = qtitem.FQUOTENO
LEFT OUTER JOIN slcdpm
ON slcdpm.FCUSTNO = qtmast.FCUSTNO
WHERE qtmast.FQUOTENO IS NULL
OR slcdpm.FCUSTNO IS NULL)
UNION
(SELECT 'Quote Master' AS [Type], count(qtmast.IDENTITY_COLUMN)
FROM qtmast LEFT OUTER JOIN slcdpm ON slcdpm.FCUSTNO = qtmast.FCUSTNO
WHERE slcdpm.FCUSTNO IS NULL)
UNION
(SELECT 'Shipping' AS [Type], count(shitem.IDENTITY_COLUMN)
FROM shitem LEFT OUTER JOIN SHMAST ON shmast.fshipno = shitem.fshipno
WHERE shmast.FSHIPNO IS NULL)
UNION
(SELECT 'Accts Recv Items' AS [Type], count(aritem.IDENTITY_COLUMN)
FROM aritem
LEFT OUTER JOIN armast
ON aritem.FCINVOICE = armast.FCINVOICE
LEFT OUTER JOIN slcdpm
ON slcdpm.FCUSTNO = aritem.FCUSTNO
WHERE armast.FCINVOICE IS NULL
OR slcdpm.FCUSTNO IS NULL)
UNION
(SELECT 'Accts Recv Master' AS [Type], count(armast.IDENTITY_COLUMN)
FROM armast LEFT OUTER JOIN SLCDPM ON slcdpm.FCUSTNO = armast.FCUSTNO
WHERE slcdpm.FCUSTNO IS NULL)
UNION
(SELECT 'Accts Payable Items' AS [Type], count(apitem.IDENTITY_COLUMN)
FROM apitem
LEFT OUTER JOIN apmast
ON apitem.FCINVKEY = (apmast.fvendno + apmast.fcinvoice)
LEFT OUTER JOIN APVEND
ON APVEND.FVENDNO = apmAST.FVENDNO
WHERE apmast.IDENTITY_COLUMN IS NULL
OR APVEND.FVENDNO IS NULL)
UNION
(SELECT 'Accts Payable Master' AS [Type], count(apmast.IDENTITY_COLUMN)
FROM apmast LEFT OUTER JOIN APVEND ON APVEND.FVENDNO = apmAST.FVENDNO
WHERE APVEND.FVENDNO IS NULL)
UNION
(SELECT 'General Ledger' AS [Type], count(GLITEM.IDENTITY_COLUMN)
FROM GLITEM LEFT OUTER JOIN glmast ON glmast.fcacctnum = glitem.FCACCTNUM
WHERE glmast.FCACCTNUM IS NULL)
UNION
(SELECT 'Journal Entry' AS [Type], count(gljeitem.IDENTITY_COLUMN)
FROM gljeitem
LEFT OUTER JOIN
gljemast
ON gljemast.fcentry = gljeitem.fcentry
WHERE gljemast.fcentry IS NULL)
UNION
(SELECT 'Jobs' AS [Type], count(joitem.IDENTITY_COLUMN)
FROM joitem LEFT OUTER JOIN jomast ON jomast.FJOBNO = joitem.FJOBNO
WHERE jomast.FJOBNO IS NULL)
UNION
(SELECT 'Receiving Items' AS [Type], count(rcitem.IDENTITY_COLUMN)
FROM rcitem LEFT OUTER JOIN rcmast ON rcmast.FRECEIVER = rcitem.FRECEIVER
WHERE rcmast.FRECEIVER IS NULL)
UNION
(SELECT 'Bill of Lading' AS [Type], count(bolitem.IDENTITY_COLUMN)
FROM BOLITEM LEFT OUTER JOIN BOLMAST ON bolmast.FCBOLNO = bolitem.FCBOLNO
WHERE bolmast.FCBOLNO IS NULL)
UNION
(SELECT 'Purchase Items' AS [Type], count(poitem.IDENTITY_COLUMN)
FROM poitem LEFT OUTER JOIN pomast ON pomast.FPONO = poitem.FPONO
WHERE pomast.FPONO IS NULL)
UNION
(SELECT 'Purchase Master' AS [Type], count(pomast.IDENTITY_COLUMN)
FROM pomast LEFT OUTER JOIN apvend ON pomast.FVENDNO = apvend.FVENDNO
WHERE apvend.FVENDNO IS NULL)
UNION
(SELECT 'RFQ' AS [Type], count(poqitm.IDENTITY_COLUMN)
FROM poqitm
LEFT OUTER JOIN
POQREC
ON POQREC.fpartno + POQREC.frev + POQREC.fac =
poqitm.fpartno + poqitm.fcpartrev + poqitm.fac
WHERE poqrec.IDENTITY_COLUMN IS NULL)
UNION
(SELECT 'Inventory Vendors' AS [Type], count(invend.IDENTITY_COLUMN)
FROM INVEND
LEFT OUTER JOIN
INMAST
ON (inmast.FPARTNO + inmast.FREV + inmast.FAC) =
(invend.FPARTNO + invend.FPARTREV + invend.FAC)
WHERE inmast.IDENTITY_COLUMN IS NULL)
UNION
(SELECT 'Current Rev' AS [Type], count(invcur.IDENTITY_COLUMN)
FROM INVCUR
LEFT OUTER JOIN
INMAST
ON (INVCUR.fcpartno + INVCUR.fcpartrev + INVCUR.fac) =
(inmast.FPARTNO + inmast.FREV + inmast.fac)
WHERE inmast.FPARTNO IS NULL)
UNION
(SELECT 'Part Cross Ref' AS [Type], count(incros.IDENTITY_COLUMN)
FROM incros
LEFT OUTER JOIN
inmast
ON (incros.fpartno + incros.fcpartrev + incros.fac) =
(inmast.FPARTNO + inmast.FREV + inmast.fac)
WHERE inmast.FPARTNO IS NULL)
UNION
(SELECT 'BOMs' AS [Type], count(INBOMS.IDENTITY_COLUMN)
FROM INBOMS
LEFT OUTER JOIN
INBOMM
ON (INBOMS.fparent + INBOMS.fparentrev + INBOMS.pfacilityid) =
(INBOMM.fpartno + INBOMM.fcpartrev + INBOMM.facilityid)
WHERE inboms.fparent IS NULL)If you have orphaned records and you want to see which records are orphaned, you can download this script and run it. It will show you details of the records found above.
What should I do about orphaned records?
Well, I would contact support to evaluate your problem and provide scripts to delete the records. If you aren’t on support, you may want to contract with a M2M consultant, such as myself, to verify that the records are un-needed and delete them.
So, what’s your score?
A Made2Manage customer called me with a serious performance problem with one of his custom reports. After I looked at it, I realized that the inventory changes in M2M Version 6.0 were the culprit. To give you some idea of his problem, the query in question ran took a few seconds before the migration and then jumped to a whopping 2.5 minutes afterward.
Since M2M Support won’t support custom reports, who you gonna call? Ghostbust….. Well no, actually me. 
What was the problem with M2M 6.0?
I remembered from Consona Connect 2008 that the inventory master table(INMAST) was essentially replaced with a sub table INMASTX and converted into a view. However, at that time, nobody could/would tell me why. Well, take a look at the create statement for the view:
/****** Object: View [dbo].[inmast] ******/
USE [M2MDATA01];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- Create new view and instead of trigger based on the renamed table.
CREATE VIEW dbo.inmast AS
SELECT *
, dbo.GetItemOnHandQuantity(fac, fpartno, frev) AS fonhand
, dbo.GetItemInspectionQuantity(fac, fpartno, frev) AS fqtyinspec
, dbo.GetItemNonNetQuantity(fac, fpartno, frev) AS fnonnetqty
, dbo.GetItemInProcessQuantity(fac, fpartno, frev) AS fproqty
, dbo.GetItemOnOrderQuantity(fac, fpartno, frev) AS fonorder
, dbo.GetItemCommittedQuantity(fac, fpartno, frev) AS fbook
, dbo.GetItemLastIssueDate(fac, fpartno, frev) AS flastiss
, dbo.GetItemLastReceiptDate(fac, fpartno, frev) AS flastrcpt
, dbo.GetItemMTDIssues(fac
, fpartno
, frev
, GETDATE())
AS fmtdiss
, dbo.GetItemYTDIssues(fac
, fpartno
, frev
, GETDATE())
AS fytdiss
, dbo.GetItemMTDReceipts(fac
, fpartno
, frev
, GETDATE())
AS fmtdrcpt
, dbo.GetItemYTDReceipts(fac
, fpartno
, frev
, GETDATE())
AS fytdrcpt
FROM dbo.inmastx
GOI’ll briefly describe what is happening here. The view loads every field from the base table IMASTX and then completes 12 separate User Defined Functions. I described how to use UDF’s here.
Why did Consona make this change?
I’d say it’s a good bet that they did so because they’ve always had problems keeping inventory numbers synced with other tables, especially with the Inventory On Hand table (INONHD). That’s why there are so many utilities to recalculate these manually. It’s important to note that M2M violates the rules of database normalization by keeping duplicates of information all over its database, and this is just one more example of it.
Great, what is the problem?
The problem, as I eluded to before, is that this kills performance, or at least the querying performance. Each one of those UDFs makes a separate call to the database, converting one select statement into many for each row.
Consider the following profile traces. When I execute the following query against the underlying table:
SELECT *
FROM inmastx
WHERE fpartno = '123456'
AND frev = '001' Profiler returns 13 rows with a total run time of 29 Milliseconds or .029 seconds.
The next trace involves the same query against the INMAST view:
SELECT *
FROM inmast
WHERE fpartno = ‘123456’
AND frev = '001' Profiler returns 49 Rows with a total runtime of 198 Milliseconds or .198 seconds.
What’s the big hairy deal? The second query is 1/5th of a second anyway.
Ah, very true, but how many parts do you have in your item master? I ran the following queries against a database with approximately 7000 part numbers. Keep in mind that you should never “Select *” from anything, but I am doing it for simplicity.
Select * from inmastx requires 1 minute 22 seconds.
Select * from inmast requires 3 minutes 4 seconds. This performance hit above is bad, but it actually gets worse. What happens if you are only retrieving fields that aren’t calculated? For example, what if you only want the part number, revision, and product class? This is common because some modules, such as shipping, do not include the product class so you must link back to the item master to retrieve it. Consider the following:
select FPARTNO, FREV, FPRODCL from m2mdata01..inmast Because INMAST is a view, all of those columns are calculated anyway and the query churns 6 times as long regardless.
So, what do I do about it?
Well, you’re going to have to edit your custom reports that deal with inventory in order to migrate, or consult with someone like me to do it for you. Essentially, you need to query the underlying table INMASTX for your data and then if you need On Hand Quantity, calculate it yourself with a sub-query. Here’s an example from above.
SELECT *
, dbo.GetItemOnHandQuantity(fac, fpartno, frev) [OnHand]
FROM inmastx
WHERE fpartno = '123456'
AND frev = '001' This isn’t a perfect solution, but in this way you can call any of the UDF’s used in that view without calling all of them.
Well smarty pants, if this is so bad, what would you do?
First, it’s Mr. Smarty Pants to you. Seriously though, I have reservations about the upgrade path with M2M. To the best of my knowledge, Consona is not making substantive database changes in Version 7. In my opinion, they should take the opportunity to normalize the database (or at least significant portions of it) and enforce referential integrity while migrating to the .Net platform. I think they should drop VFP entirely, train users to use SQL Reporting Services for reports, and start fresh in version 7.
What do you think?
Brief Summary – M2M orphans records in the Quote Master and Quote Items tables when the quotes are in the Cancelled status.
As I mentioned last week, I came across a problem with orphaned records in my addressing tables. Rather than address my orphaned record problems piecemeal, I decided to create a script to check most of the major tables for orphaned records. However, when I started working on quote tables, I found almost 300 orphaned records in the Quote Items Table.
This gave me pause because I was worried that my script wasn’t correct, and that I would delete potentially viable data. After investigating further I came to the conclusion that M2M orphans cancelled quotes as a rule. What is particularly weird is that the M2M screen will actually show you the quotes. However, the data is worthless because you cannot Add and Carry from it.
How did you figure that M2M orphans cancelled quote records?
Well, by testing, of course. I took the following steps:
- Fired up my test server and opened the practice company containing the educational database.
- Manually deleted all of the Quote Master (QTMAST) and Quote Items (QTITEM) tables.
- Created a Prospect (PROS) and then created a quote for said prospect.
- Attempted to delete the prospect in Started and Open statuses and received the following errors.
 
- Changed the status to Cancelled and M2M allowed me to delete the prospect.
- Completed the previous steps using a Customer rather than a Prospect to the same effect.
I retrieved the SQL statements using Profiler while testing and found the culprit. I still intend to explain how to use SQL Profiler for M2M Administrators. If you look at the code below you’ll notice that it doesn’t mention Cancelled quotes.
SELECT @Status = RTRIM((SELECT TOP 1 fStatus
FROM Qtmast
WHERE fcustno = @CustNo
AND RTRIM(UPPER(fstatus)) IN('AWAITING APPROVAL', 'STARTED', 'OPEN')))Perhaps Consona developers intended the code to allow deletions of prospects and customers if they only had cancelled quotes, but the least they could do is clean up those records in the deletion process as well.
I’ll follow up in a week or so with the complete script to find orphaned records in your database.
Recently I got a call from another M2M Consultant who was attempting to install M2M on Windows 7. He simply could not get VBA to work. I asked him to run through the steps and I found his problem right away. He had made a mistake early in the process; I have made similar mistakes as well. I have ranted in an earlier article, about the confusing, tedious process of installing VBA for Made2Manage.
However, the consultant assured me he had followed the instructions properly. When I checked the instructions on the Made2Manage Version 6.0 DVD I found the following:
1. Copy the entire \Made2Manage Optional Modules\VBA folder and all its sub-folders from the DVD to a new VBA folder in your …\m2mwin\dtk folder. The m2mwin directory is the directory where the m2m.exe executable is installed and may be different from machine to machine.
2. In the m2mwin\dtk\vba folder, double-click the VBA6.msi file. This will install VBA.
Do not do this. Doing so will cause your VBA install to fail. The correct instructions should be to copy the contents of the VBA file from the install source, but not the VBA folder itself.
But Wait! There’s More!
When I checked the install instructions from the M2M Version 5.6 CD the instructions were incorrect as well. Doesn’t anyone check this stuff? M2M 5.6 has been out for approximately three years.
Anyway, the correct, albeit poorly written, instructions for M2M 5.6 (and presumably for 6.0 as well) are below and were taken from M2M Expert.
Steps to install VBA to ensure proper registry and functionality
If VBA is not registered properly, users will not be able to login to Made2Manage without errors. The following describes the way to install VBA for proper functionality.
1. Obtain the CD containing VBA for your current Made2Manage system.
VBA is version specific, so with each upgrade, each computer must have VBA re-installed. Please see note [1].
2. Navigate to the VBA sub-folder on the CD.
3. Copy the entire VBA folder files and sub-folders from the install CD to your local drive’s DTK sub-folder within the Made2Manage program folder.
You will be replacing the files and sub-folders within but not including the VBA parent folder from the CD’s VBA folder into the DTK root folder. Opt Yes to overwrite.
The Made2Manage program folder is where your shortcut points, which shows in the properties of the program icon. Alternatively, from the windows Start Bar, click the find files or folders menu option and search for the m2m.exe file. Within this program folder will be the DTK folder to designate as the target folder.
Example: c:\program files\made2manage\dtk
The source folder from the CD is the contents of the VBA CD folder, not including the actual VBA parent folder.
At the conclusion of the copy you will have the same local DTK folder (c:\program files\made2manage\dtk) with a set of files and folders under it which does not include a VBA sub-folder, just the contents of the CD VBA folder. E.g., c:\program files\made2manage\dtk\vba will not exist after the copy, but 7 other folders will exist, including c:\program files\made2manage\dtk\cc.
4. Drill to the DTK folder within the Made2Manage program folder on your local drive. Double click the VBA6.msi file to start the VBA install.
5. Copy the M2V.app file from the DTK sub-folder to the Made2Manage program folder. The target folder will be that containing the m2m.exe file. The source folder will be the DTK sub-folder.
Example: Copy file c:\program files\made2manage\dtk\m2v.app to c:\program files\made2manage
If given an option to replace, opt yes.
6. Copy the M2VInterface.exe from the DTK sub-folder to the windows system folder.
a. The system folder is specific to your operating system. It will usually be either c:\windows\system32 or c:\windows\system.
b. If you have both the system and system32 folders, check to see if the m2vinterface is already in one. If so, that is the one to replace.
c. If the m2vinterface file is in both system and system32, delete one occurrence and replace the other with that of the DTK folder.
d. If the m2vinterface isn’t in either, copy it to the system32 sub-folder
7. Double click the newly placed m2vinterface.exe file.
a. The double click should be done within the target windows system folder to which the m2vinterface was copied.
b. Nothing will appear to happen which is the correct result.
c. Double clicking the m2vinterface file will self register it.
8. Check M2MExpert to see if there are any VBA service pack or hot patch files.
For example, version v5.6 has a replacement m2v.app. Download and install or copy/replace any patch on M2MExpert.
9. Launch Made2Manage.
10. Open the User Management window ( USER )
Locate your user name record, modify, and check the box for VBA Developer. This will allow you to right click on the form and select components or macros
11. Repeat the installation process on any computer that needs to access or use VBA forms.
Any questions?
 The Princess Bride The other day I found myself in a meeting regarding a CRM package we are implementing. The implementation company had brought two project managers, a sales lady, and a geek who will actually assist in the implementation. I sat in on this meeting with some of our executives and sales people.
I was regaled with your typical clichés and buzzwords including:
- Deliverables
- Success Factors
- Action Items
- Methodologies
- Thinking outside the box.
The project manager who has PMP certification reads through slide after PowerPoint slide. Each slide includes numerous bullet points each having the same cheesy metal hitting sound effect. Their sales people are really bonding with our sales people because both groups live for this stuff.
At one point I literally laughed out loud because the PMP was listing possible risk factors to project success and he actually listed “Acts of God.” He then proceeded to explain to us what “Acts of God” were such as earthquakes, hurricanes, etc. As if there was a devastating earthquake, we wouldn’t expect the project to be delayed.
To be clear, I am not suggesting that the meeting wasn’t necessary. I’m just suggesting that the meeting should have been condensed into less than 10 minutes.
Anyway well into the second hour, I am barely clinging to consciousness. In an effort to stay awake, I look around the room and my eyes lock with those of their geek. The eyes are the windows of the soul… I could tell right then that I had a kindred spirit; the only other person in the room who recognized as a colossal waste of time. For some reason, my mind went back to the scene in The Princess Bride where Count Rugen takes Westley prisoner.
I just wish I could have messaged him, “We are men of action, meetings do not become us.”
I found more orphaned records recently when querying a M2M table with an order by clause. Once again, this occurs because in my opinion the M2M database is not properly designed. Here’s a quick walk-through on how I found it. Incidentally, the screen shots are from TOAD using M2M’s practice database.
A manager tasked me with cleaning up our customer and address tables which consists of looking for real world customers having multiple customer numbers in M2M. When looking into the address table, I used a script similar to:
SELECT fcaliaskey AS [CUST NO]
, fcaddrkey AS [Add Key]
, fccompany AS [Company]
, fccity as [City]
, fcstate as [State]
, fczip as [Zip]
FROM M2MDATA66.dbo.syaddr
WHERE fcalias = 'SLCDPM' The reason I use SLCDPM is because I am looking for customers, if I was trying to clean up vendors the fcalias would be ‘APVEND’. Anyway, that script returns a dataset which looks like this:
With the exception of some missing zip codes, the data set looks fine. To simulate orphaned records I artificially introduced some blank fcaliaskey records using the following script:
UPDATE M2MDATA66.dbo.syaddr
SET fcaliaskey = ''
WHERE fcaddrkey = '0003' Incidentally, the fcaliaskey field should never be blank, but I found several records in my production database that were. Anyway, we receive the message that 4 rows are affected. However, if you re-run the query above, the dataset (that you can see on screen) looks the same. It’s only after you apply an Order By clause that you find the problem.
 Oops
Now for those who write SQL, this is not earth shattering information, but there’s a reason I’m sharing it with you.
How Do You Deal With Technical Hypochondria?
 What? Who?
Above I described how I found more orphaned records recently when querying a M2M table with an order by clause. Technically this is data corruption, but when people hear that term they tend to panic.
The Manager I was dealing with is what I call a technological hypochondriac. He is an old school accounting type who doesn’t trust those d*mned computers. He keeps paper records for everything, saves his adding machine paper tapes, spends weekends at work double checking even insignificant numbers, etc.
So, you’re the DBA. You’ve found orphaned records, records that cannot possibly be used in your ERP system because the field which would have linked them, in this case the customer number, is empty.
What do you do?
Page 11 of 27« First«...910111213...20...»Last »
|
|
Popular Articles