SQL Administrator Skills Required to Support Microsoft Great Plains

Microsoft Great Plains is becoming more and more popular and we see new purchases among midsize and large corporations, where, in our opinion, Microsoft SQL Server DB administrator position is a must. If you are database administrator and you are part of the decision making team, purchasing Microsoft Business Solutions Great Plains for your corporation, we would like to set your expectation on the level of SQL knowledge, skills and experience needed to support Microsoft Great Plains in-house

? Data querying. Microsoft Great Plains has decent ReportWriter, which allows users to create and modify existing reports, however the recommended tool, and you will soon understand that if you need really challenging reports (Sales Commissions, Bill of Lading, Sales Order Status with Purchasing info, Regional Sales, etc.) ? you need to deploy Crystal Reports or Crystal Enterprise. Crystal Report itself is usually based on the view or stored procedure. So ? you should expect to be familiar with Great Plains tables architecture and be capable to create select statement with multiple joins and unions

? Data Repair. You should be ready to save GP table prior to data repair: select * into SOP10100_Copy from SOP10100 and then use update statement to fix incorrect values. If you need to load SOP10100 back from the backup table ? first drop DEX_ROW_ID column and then use: insert into SOP10100 select * from SOP10100_Copy.

? Backup and Restore. Great Plains has DYNAMICS database which stores system info, such as users, security, companies, etc. and each company has its own database. You should be able to restore mostly company databases from the backup. This seems to be trivial routine.

? Technical Support. When you have issues with posting or need help from Microsoft Business Solutions Technical Support, you place case through customer source and you should expect the technician provide you with SQL scripts to fix your data.

? Some Useful Scripts. You need to be able to delete locked user: delete activity where userid='TOM', also very popular is batch recovery script, you need to unlock the batch in SY00500 table: Posting Definition Master

? Do not expect. Replications (except probably snapshot) ? it adds GUID column and then Great Plains Dexterity runtime can not upgrade the modified tables. Referential Integrity is not used, Varchar or NVarchar are not used ? the reason is ? Dexterity architecture, which was initially designed with database independence in mind. Logins and access management ? Great Plains uses its own security, which is translated into SQL logins with encrypted password.

? Moving Great Plains on the new SQL Server. It is often confusing for the DB Admin. Besides physical move through backups or detach/attach DB you need special scripts to transfer GP security and users logins, these scripts are available through MBS tech support or feel free to call you MBS partner.

? Advanced Techniques. If you need to notify your GP users on the specific event in GP ? such as invoice was paid, you can deploy SQL emailing: xp_send_mail. This procedure could be called in the trigger for the apply payments table.

? Data Integration. XML inbound and outbound streams for the integration stored procedures is becoming defacto standard. Also you should be familiar with heterogeneous SQL queries: OPENROWSET and linked server conceptions.

Happy implementing! You can always appeal to us to help you with your system. Give as a call 1-630-961-5918 or 1-866-528-0577, help@albaspectrum.com

Andrew Karasev is Chief Technology Officer in Alba Spectrum Technologies ( http://www.albaspectrum.com ), Microsoft Business Solutions Great Plains, Microsoft CRM, Navision, Microsoft RMS, Microsoft Business Portal customization company, serving clients in Chicago, Boston, New York, Miami, Atlanta, Houston, Dallas, Denver, Los Angeles, San Francisco, San Diego, Seattle, Minneapolis, Phoenix, Toronto, Montreal, Brazil, Mexico, UK, Australia, Canada, UK, Europe, Russia an having small offices in multiple states and internationally.

---

Demand More From Your Lead Tracking Software

An integral part of any quality CRM system is lead... Read More

5 Mac Security Tips You Can?t Live Without

So, you've bought a new Macintosh, and now you may... Read More

Microsoft Great Plains Jewelry ? Implementation & Customization Overview

Each Industry and market niche has business specific and unique... Read More

Software Upgrades Arent Always the Best Move

When my daughter was getting into AOL instant messaging (AIM)... Read More

OS 101: Suggestions for Choosing an Operating System

An operating system (abbreviated OS) is essentially the path through... Read More

How To Develop Software For Your Business

Software development is a risky business.Many software developers are barely... Read More

Most Common Ways to Accumulate Spyware (where It is Downloaded to Your PC)

It is possible that if one avoided all sources of... Read More

What is Fleet Maintenance Software?

Simply put, fleet maintenance allows companies to monitor and maintain... Read More

Corel WordPerfect 7 Macro Programming Example

Case study: A secretary using Corel WordPerfect 7 is often... Read More

Snort for Network IDS

What is Snort?Snort is an open source network intrusion detection... Read More

Keeping Track

One of the things we can be as certain of... Read More

Microsoft Great Plains: Annual Enhancement Program ? How To Be Re-Enrolled

Microsoft Business Solutions Great Plains is mid and even corporate... Read More

Document Templates Give You The Perfect Framework For Your Documents

When it comes to running an office, the SOHO entrepreneur... Read More

Unofficial Windows 98 SE Service Pack 2.0 RC2

Microsoft has never released a service pack for Windows98 SE,... Read More

Microsoft Great Plains Payroll Module Customization Scenarios

It is now common thing when large corporation selects mid-market... Read More

Did You Ever Want to Completely Erase Everything on Your Computer?

Did you ever want to erase everything on your computer?... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the... Read More

ERP Software Financing: the Future? ? Overview for Company Owner

In our case ? we serve Microsoft Business Solutions ERP... Read More

Adware and Spyware Blockers

The most important things you can do for your computer... Read More

Microsoft CRM Data Import FAQ

Microsoft CRM has built-in conversion tool, however you should probably... Read More

Who?s Watching What You Type?

If someone entered your home, uninvited and installed numerous cameras... Read More

Brand Your Websites URL With a Favicon

Have you ever noticed that when you look at your... Read More

Best Spyware Removers

Finding the best spyware removers to detect and remove spyware... Read More

ERP Remote Support: Microsoft Great Plains Analysis ? Pluses & Minuses

Former Great Plains Software Dynamics/eEnterprise and currently Microsoft Business Solutions... Read More

Software Tools To Help Your Business Sell More

Sales are all about leverage, because there is only so... Read More

Separate Anti-Keylogging Protection: Who Needs it Most?

If there still are few unprotected computers left, I haven't... Read More

Navision Attain Database access via C/ODBC in ASP.NET Application

Navision Software was purchased by Microsoft and now it is... Read More

Selecting Corporate ERP: Microsoft Business Solutions Great Plains ? Estimation Parameters

As we could imagine, if you are reading this article... Read More

C++ Tutorials: 3, Program Flow (If, Else, While, For)

Program Flow is what you think it is. How the... Read More

Reloading Windows XP

If you have been running Windows XP for a couple... Read More

Microsoft Great Plains Food Processing ? Implementation & Customization Highlights

Microsoft Great Plains might be considered as ERP platform to... Read More

How to Build a Basic CSS Layout

Designing without tables by using CSS layouts is fast becoming... Read More

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More