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.

---

Microsoft Great Plains Jewelry ? Implementation & Customization Overview

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

Reduce Pop-ups and Annoying Ads

There is many things more frustrating than surfing a website... Read More

Corporate ERP Selection: Microsoft Great Plains

In our opinion, traditional approach when you select ERP/MRP system... Read More

How to Build a Basic CSS Layout

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

Microsoft Great Plains POP: Purchase Order Processing ? Overview For Consultants

Great Plains Purchase Order Processing (POP) module makes up one-third... Read More

The Death of Windows

I have always regretted how Microsoft price gouges and rips... Read More

Groupware as a Document Manager: Collaboration Series #3

This article is the third of a series of articles... Read More

Microsoft Blues

With the advent of 'Service Pack 2' for Windows XP... Read More

Microsoft Great Plains: Manufacturing or Bill of Materials - Overview for IT Specialist

Microsoft Great Plains is main Microsoft Business Solutions product, targeted... Read More

Blind CC (Bcc): Master Its Use When E-Mailing

If you use Microsoft Outlook (or similar applications) for e-mailing,... Read More

SSH (SCP) Send Files from Windows to Your Linux Box

Not all of us have the luxury of working both... Read More

Microsoft CRM Integration with IBM Lotus Notes Domino ? Machinery Dealership Example

IBM Lotus Notes with Domino email server is traditional document... Read More

Microsoft Great Plains Reporting ? Overview for Developer

Looks like Microsoft Great Plains becomes more and more... Read More

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance... Read More

ERP Implementation: Success Factors

As seeing large number of implementations ? in our case... Read More

Five Reasons for Using an O/R Mapping Tool

So, why should you use any O/R mapping tool? I... Read More

Preventive Maintenance Software Companies

Several software companies design programs for preventive maintenance. Most of... Read More

Pros and Cons of Using FREE Software in Your Business

Itâ??s easy to understand why you might be drawn to... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

Microsoft Great Plains Customization Recovery & Upgrade for Large Corporation

At the end of XX century, in the late 1990th... Read More

Microsoft Great Plains: Getting New Users Licenses ? Annual Service Plan FAQ

Microsoft Business Solutions Great Plains, Solomon, Navision, Axapta, Microsoft CRM... Read More

Tools for Customizing Great Plains

Microsoft Business Solutions ? Great Plains has captured the US... Read More

Microsoft Great Plains GL: General Ledger ? Overview For Consultant

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

The End of Spyware?

The US House of Representatives has recently passed the "Spy... Read More

Five Steps to Rapid Development with TierDeveloper 4.0

Follow the steps below to quickly design, generate, and deploy... Read More

SQL: Querying Microsoft Great Plains ? Overview for Database Administrator/Developer

Looks like Microsoft Great Plains becomes more... Read More

Make or Break Factors - When Considering Estimating Software

Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More

.NET :Solving the Multiple Inheritance Issue Under .NET Platform

.NET platform does not support multiple inheritance. Do not confuse... Read More

Microsoft Great Plains ? Licensing & Product Versions

Current Microsoft Business Solutions Great Plains has more that 10... Read More

IBM Lotus Domino or Microsoft Exchange?

IBM Lotus Domino or Microsoft Exchange?The severe competition continues for... Read More

Software: What Suits Me?

Almost all new and major brand of PCs come with... Read More

Document Templates Give You The Perfect Framework For Your Documents

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

The Hidden Power of Online Manual

Writing software manuals is boring, isn't it? We often think:... Read More