Digging through Dynamics: GP stored procedures is painful, to say the least.

When you pull the SQL out of the database via SQL Server Management Studio it is one LONG line. No formatting, nothing. Reading the TSQL as one line isn't really an option. Reformatting it by hand takes FOREVER.

Fortunately, there is a wonderful online formatter over at  TSQLTidy. It can transform the GP stored procedures into something that can actually be parsed by a human brain.  TSQLTidy also has a  web service. Theoretically this is a  SOAP interface that will allow the creation of scripts/programs that can then use  TSQLTidy for its formatting. I've been trying to get it to work via  Python using  ZSI. However, I have had no such luck yet. Eventually.

Having recently upgraded from GP8 to GP10 and having switched from Horizons Manufacturing to GP: Manufacturing, I wanted to lay out a few do's and don'ts for anyone else considering a switch. Hopefully the below will save you some of the headache.

1. Take The Defaults: As with pretty much all Microsoft products, accepting the defaults is prudent and nigh a necessity. If you simply take the defaults and use the software exactly as designed, you'll have an acceptable experience.
2. Do not customize: This may seem to be the same as above. It kind of is. However, it's more than that. Great Plains can be customized simply through the various settings and incomprehensible menus it offers. However, you can really customize Great Plains with VBA and/or Dynamics code. Don't. Just Don't. No, really, DON'T!
3. Do not integrate 3rd Party Products: They're not worth it. Great Plains is a black box. Even to Microsoft. You can't know what's it's doing. It's one of the universe's great mysteries.
4. Do not roll-your-own Product: If you are masochistic and really enjoy pain, go right ahead. We have specific manufacturing needs. One of which is integration with our hand-held barcode scanners. We are now on the second generation of hand-held software. The first was abominably designed. The second has a decent architecture, if you exclude the whole Great Plains part. As stated above, no one knows the innards of Great Plains. It's like a  Mord-sith. Once you start looking and turn your power over to it, it makes you writhe in pain forevermore. Doing a quick count, I turned up 1,988 tables in my Great Plains database. This excludes the tables it uses in the DYNAMICS database as well as master. It also does not take into account the hundreds of temporary tables it creates during normal usage. 1,988 tables! That's insane. One can not understand Great Plains. The whole concept of understanding is anathema to Great Plains.
5. Don't Use Lots of Work Centers: If you see yourself using lots of work centers, you're doing it wrong. Especially if you have lots of items. This can be partially mitigated as long as an item only has a small set of possible work centers. If you're like us, with >900 work centers and >6,000 items, you're simply in for a long wait.

I hope that these tips will help. If you're already a Great Plains user, and are considering upgrading, ask yourself if it's really worth it. It's probably less painful to upgrade than switch to a new system. It isn't, however, pain-free.

I am not a Great Plains expert. I'm just a poor hapless IT manager thrown into the woes of maintaining, upgrading, supporting, and developing against Great Plains. If you know of a true Great Plains expert, please let me know. My requirements for a Great Plains expert are as follows:

• Knows a majority of the Great Plains tables by their physical name
• Knows what these tables do and how they are related
• Proficient at reading TSQL
• Proficient at maintaining and managing MS SQL Server
• Knows what is happening behind the scenes for a majority of the windows in the GP interface
• Can explain why Microsoft decided to go against most every UI interface and interaction convention in existence
• Can explain why Dynamics presents you with the most misleading lists and scroll bars ever created (Since when do lists start in the middle with no indication that there are actually items above the one at the top of the list?)
• Can explain why Great Plains lets you do things that break the consistency of your data
• Can explain why more operations in Great Plains aren't atomic.
• Is not content with simple answers and must understand the why behind the answer.

If you are, or know of some one that fits the requirements, please contact me.

By the way, if you want to know how to list the number of tables in a MS SQL database, here you go:

select count(*) as TableCount from Information_Schema.Tables where Table_Type = 'BASE TABLE';