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 Integrations ? Retail Management Sample

Microsoft Business Solutions is emerging as very attractive vendor for... Read More

Lotus Notes Domino and Web: Application Development ? Tips for Programmers

Beginning with Domino version R4 it has integration with the... Read More

Tripwire for Linux File Integrity

What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More

C++ Function Templates

C++ Function templates are those functions which can handle different... Read More

Preventive Maintenance Software Companies

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

Free PDF Publishing Software

In a previous article, I wrote about OpenOffice... Read More

Software Automation Helps Increase your Bottom Line

When you own a small business, time is money. And... Read More

Microsoft Great Plains version 8.5: Upgrade, Customization, VBA, Crystal Reports - Highlights

Microsoft Great Plains is one of the Microsoft Business Solutions... Read More

What is Preventive Maintenance Software?

Preventive Maintenance (PM) is defined as scheduled work done on... Read More

Mozilla Vs. Firefox

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

Microsoft Great Plains & CRM in Transportation & Logistics ? Overview

Microsoft Business Solutions Great Plains and MS CRM (client relation... Read More

A Time-Saving Programming Tactic that Doesn?t Work

Let's say that you have a software project that's under... Read More

Adware, What Is It?

Adware is a type of Spyware program that displays some... Read More

Spyware Definitions List

The adware and spyware definitions list is very long. But... Read More

Microsoft CRM and Great Plains Implementation: Freight Forwarding Business Automation Example

Microsoft Business Solutions offers several ERP applications: Great Plains, Navision,... Read More

Inherent Dangers Of File Sharing Via The Internet.

Cyberspace has opened up a new frontier with exciting possibilities... Read More

A Simple Guide To Wikis

A wiki is an editable text-based website. But you don't... Read More

Cisco Certification: Five Things To Do DURING Your CCNA Exam

There are plenty of articles out there about how to... Read More

Getting Patched with Windows Service Pack

Are you one of those people that keeps track of... Read More

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More

The Tools and Features of Adobe Photoshop

Adobe Photoshop is one of the world's leading graphics editing... Read More

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

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

Crystal Reports For Microsoft RMS ? Overview For Developer/Report Designer

If you are software developer or database administrator - we... Read More

Microsoft Great Plains Remote Support - Overview

Microsoft Great Plains is now standard mid-market ERP application, serving... Read More

Microsoft Great Plains international implementation ? USA / Mexico ? overview for consultant

Microsoft Business Solutions Great Plains was purchased from Great Plains... Read More

25 Things Mapping Software Can Do For You

1. With mapping software you can create a report that... Read More

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW ? ReportWriter ? Tips for Developer

Microsoft Business Solutions Great Plains is written in Great Plains... Read More

Reduce TCO: The Java Database Way

TCO (Total Cost Ownership) is the buzzword in... Read More

.Net Charts and Graphs Interact with Businesses and Customers

Bar charts, bar graphs, and any other chart or graph... Read More

Professional Software Icons For Your Standalone Application

User interfaces and accessibility are some of the most important... Read More

Microsoft Great Plains Middle East - Arabic Language Support

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

Microsoft Great Plains - Typical Problems And Fixes ? Overview For IT Administrators

How to delete the user? This is the first problem... Read More

Microsoft Great Plains Implementation: Collection Management ? Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More